Total Sum of % calculations

[miaklarna]

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! 🙂

[miaklarna · Accepted 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]))

[v-heq-msft]

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

[Greg_Deckler]

@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