Forum Discussion
Multiply total with %share in another table
Hi, I have a measure that calculates the total for a certain account number among numerours accounts: Total EUR Account1 = CALCULATE('GL '[Total EUR],Account[Account number]="1") and then put in a table to show cost per financial quarter:
FQ1 | FQ2 | FQ3 | FQ4 | Total |
100 | 200 | 400 | 600 | 1300 |
Table 1
Now this sum per quarter I want to split into four different categories based on another table:
FQ | Category | % share |
1 | A | 10 |
1 | B | 30 |
1 | C | 40 |
1 | D | 20 |
2 | A | 50 |
2 | B | 10 |
2 | C | 10 |
2 | D | 30 |
3 | A | 25 |
3 | B | 25 |
3 | C | 25 |
3 | D | 25 |
And so on... |
|
|
Table 2
So I get the below table where the % share per Category and FQ has been multiplied by the total for the applicable FQ.
I do not have the categories in the source data for the main table (Table 1)
Category | FQ1 | FQ2 | FQ3 | FQ4 | Total |
A | 10 | 100 | And so on... |
|
|
B | 30 | 20 |
|
|
|
C | 40 | 20 |
|
|
|
D | 20 | 60 |
|
|
|
Table 3 (wanted result)
Thankful for any helpful ideas!
6 Replies
- v-diye-msft
Community Support
Hi Anonymous
Kindly check my below results, I do some transformation in the source table and then manage the relationship as well as add the measure:
Pbix attached
Hi v-diye-msft,
Thanks for you help, it seems work partly; the row, column totals and grand total are not correct?
If that can be fixed I think we´re done. Would aprreciate any help on that, thanks!
It also seems I don´t get corect numbers in the table since I acutally also now have years in my data, so I have e.g. FQ 1 2018, FQ 1 2019, FQ 2 2018, FQ 2 2019, and I suspect that I need to adjust the use of MAX-function somehow, but how?
- v-diye-msft
Hi Anonymous
check this one:
pbix attached
Hi v-diye-msft
I now have the follwoing formula which almost gives me the correct result, except for the grand total (please see yellow marked in below picture). Any ides on how that can be fixed?
amitchandak; any ideas on above?
