Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear community,
I am stucked with the following question. I have 3 fact tables (they unfortunately cannot be merged to one table). I am performing a following actions to get a result for remote revenue based on remote volumes / total volumes * total revenue
Table Revenue:
Jan | Feb | Mar | Total | |
ProductA | 5 | 7 | 3 | 15 |
Product B | 10 | 8 | 10 | 28 |
15 | 15 | 13 | 43 |
Table Volumes:
Jan | Feb | Mar | Total | |
ProductA | 10 | 35 | 12 | 57 |
Product B | 10 | 16 | 32 | 58 |
20 | 51 | 44 | 115 |
Table remote Volumes:
Jan | Feb | Mar | Total | |
Product A | 4 | 15 | 8 | 27 |
Product B | 4 | 8 | 12 | 24 |
8 | 23 | 20 | 51 |
The final table with the following dax gives the result that is not correct in total (both for rows and columns).
remote_%_total_revenue =
VAR remotevolumes = SUM('fact_remotevolumes'[volumes])
VAR totalvolumes = SUM('fact_volumes'[volumes])
VAR totalrevenue = SUM('fact_revenue'[revenue])
RETURN
totalrevenue * DIVIDE( remotevolumes, totalvolumes)
Output (please see in red correct total):
Calculated Remote Revenue (% of total) | |||||
Jan | Feb | Mar | |||
Product A | 2,0 | 3,0 | 2,0 | 7,1 | Correct result: 7 |
Product B | 4,0 | 4,0 | 3,8 | 11,6 | Correct result: 11.8 |
6,0 | 6,8 | 5,9 | 19,1 | ||
Correct result: 6 | Correct result: 7 | Correct result: 5.8 | Correct result: 18.8 |
I applied following DAX. However the result is correct in total columns but still not correct for total rows and for grand total. The nested SUMXs did not help.
remote_revenue =
CALCULATE(
SUMX(
'dim_products',
[revenue] * [remote_%_total_volumes]
))
Output:
Jan | Feb | Mär | |||
Product A | 2,0 | 3,0 | 2,0 | 7,105263158 | Correct result: 7 |
Product B | 4,0 | 4,0 | 3,8 | 11,5862069 | Correct result: 11.8 |
6,0 (it worked) | 7,0 (it worked) | 5,8 (it worked) | 19,06956522 | ||
Correct result: 18.8 |
Does anyone have a hint what should be a solution for correct sum?
Thank you! 🙂
Solved! Go to Solution.
Thanks a lot for your help! I tried both solutions but it did not work for my data.
Meanwhile I figured out why my nested SUMX did not work. Before going to the row context for month, I needed to summarize a table.
I adjusted DAX a bit and it worked at the end.
Here is a DAX:
remote_revenue =
SUMX(
'dim_products', SUMX(SUMMARIZE(dim_calendar, dim_calendar[MonthYearNum], "MonthYear", dim_calendar[MonthYearNum]),
[revenue_total] * [remote_%_total_volumes]))
Thanks a lot for your help! I tried both solutions but it did not work for my data.
Meanwhile I figured out why my nested SUMX did not work. Before going to the row context for month, I needed to summarize a table.
I adjusted DAX a bit and it worked at the end.
Here is a DAX:
remote_revenue =
SUMX(
'dim_products', SUMX(SUMMARIZE(dim_calendar, dim_calendar[MonthYearNum], "MonthYear", dim_calendar[MonthYearNum]),
[revenue_total] * [remote_%_total_volumes]))
Hi @miaklarna ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
fact_remotevolumes
fact_revenue
fact_volumes
Open Power query and select custom column
Create calculate column
Column = 'fact_remotevolumes'[volumes] / RELATED(fact_volumes[volumes]) * RELATED(fact_revenue[revenue])
Final output
Best regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@miaklarna First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |