英文:
Dashboard to compare different companies with different currencies
问题
我有一张包含多家公司的表格。
| 店铺 | 货币 |
|---|---|
| S1 | € |
| S2 | € |
| S3 | kr |
| S4 | £ |
| S5 | € |
还有一张包含汇率的表格
| 货币 | 汇率 |
|---|---|
| € | 1.00000000000 |
| £ | 1.17100000000 |
| kr | 0.09730000000 |
在使用这样的公式时
YearRevenue = SUM(Shop[Revenue]) * SUM(Currency[Exchange rate])
逻辑上它现在做的是将所有收入加到一个堆中，并将所有的转换率加到一个堆中。
所以现在发生的是所有的收入将乘以2.2683。
我希望这可以分别计算每家店铺，然后在最后汇总。
我认为有两种选择：
- 是添加一列，根据汇率计算收入，这样我就不必使用公式。
- 一个我不知道如何处理的公式。
英文:
I have a table with multiple companies.
| Shop | Currency |
|---|---|
| S1 | € |
| S2 | € |
| S3 | kr |
| S4 | £ |
| S5 | € |
And a table with the exchange rate
| Currency | Exchange rate |
|---|---|
| € | 1.00000000000 |
| £ | 1.17100000000 |
| kr | 0.09730000000 |
When using a formula like this
YearRevenue = SUM(Shop[Revenue]) * SUM(Currency[Exchange rate])
Logically what it now does is add all revenues in 1 pile and add all the conversions rate in 1 pile.
So what happens is all the revenue will be multiplied by 2.2683
I want this to be calculated for each shop individually and sum up at the end.
I think I have 2 options:
- Is to add a column with the calculated revenue based on the exchange rate so I don't have to use a formula.
- A formula which I have no idea on how to aproach.
答案1
得分: 1
你可以使用 SUMX，你的表达式应该是这样的：
YearRevenue = SUMX(Shop,Shop[Revnue]*RELATED('Currency'[Exchange rate]))
确保你的两个表格在 Currency 列上建立了多对一的关系。
英文:
You can use SUMX, your expression would be like this:
YearRevenue = SUMX(Shop,Shop[Revnue]*RELATED('Currency'[Exchange rate]))
Make sure that your 2 tables are linked with a many to one relationship on Currency column.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论