Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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?
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?
Hi @v-diye-msft ,
Thanks a lot, we´re getting closer!
I get the correct totals on FQ-level, but on total year level there is not sum, can I add year to the ISINSCOPE-function somehow?
I´m also calculating sum last year using the formula;
and also calculating "% diff this year vs last year", using the formula:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |