How to get correct subtotal and percentage

[studyri10]

Hi All,

I have below 4 tables, what I need is to calculate the amount of column name NEED.

I try CALCULATE, FILTER, ALL, ALLEXCEPT can't get the result I want.

Can anyone give me a suggestion? thank you so much.

TABLE1		TABLE2			TABLE3			DataTable
L1		L1	L2		L2	L3		L3	AMT	NEED	NEED
A		A	AA		AA	AA1		AA1	75	100	75%
E		E	EE		AA	AA2		AA2	25	100	25%
P		E	EEE		EE	EE1		EE1	100	100	100%
		P	PP		EEE	EEE1		EEE1	10	100	10%
		P	PPP		EEE	EEE2		EEE2	20	100	20%
		P	PPPP		EEE	EEE3		EEE3	70	100	70%
					PP	PP1		PP1	50	50	100%
					PPP	PPP1		PPP1	80	80	100%
					PPPP	PPPP1		PPPP1	100	500	20%
					PPPP	PPPP2		PPPP2	300	500	60%
					PPPP	PPPP3		PPPP3	100	500	20%

[v-rzhou-msft · Accepted solution]

Hi @studyri10 ,

My data model is as below.

Please try code as below to create calculated columns.

Need Sum = 
CALCULATE(SUM('DataTable'[AMT]),ALLEXCEPT('DataTable', TABLE2[L2],TABLE1[L1]))

Need Percentage = 
DIVIDE('DataTable'[AMT],'DataTable'[Need Sum])

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@studyri10 What is the result that you want?

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