Drill Down on Different measures on Matrix

[Narukkp]

Hi Team,

I have the data as below data format and required the output as first level total sum required, second level each state wise percentage out of total , 3rd level each district wise percentage out of state total wise. For data and required output please find below sample formats

Data
State	District	Branch	Value
State1	District1	Br1	4
State1	District1	Br2	6
State1	District2	Br3	15
State1	District2	Br4	5
State2	District3	Br5	3
State2	District3	Br6	6
State2	District4	Br7	7
State2	District4	Br8	4
State3	District5	Br9	10
State3	District5	Br10	20
State3	District6	Br11	14
State3	District7	Br12	8

Required Output
Total	102
	State1	29%
		District1	33%
		District2	67%
	State2	20%
		District3	45%
		District4	55%
	State3	51%
		District5	58%
		District6	42%

[v-jayw-msft · Accepted solution]

Hi @Narukkp ,

Please refer to the measure below and see if the result achieve your expectation.

Measure = 
var district = CALCULATE(SUM(Data[Value]),ALLEXCEPT(Data,Data[District]))
var state = CALCULATE(SUM(Data[Value]),ALLEXCEPT(Data,Data[State]))
var total = CALCULATE(SUM(Data[Value]),ALL(Data))
var selected_state = SELECTEDVALUE(Data[State])
var selected_discrict = SELECTEDVALUE(Data[District])
return
IF(ISBLANK(selected_state)&&ISBLANK(selected_discrict),total,IF(ISBLANK(selected_discrict),FORMAT(state/total,"percent"),FORMAT(district/state,"percent")))

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Narukkp]

Hi @ v-jayw-msft Thanks you so much for your solution

[amitchandak]

You can try isfiltered

if(ISFILTERED([District]),formula1, formula2)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[rajulshah]

Hello @Narukkp,

Please try the following DAX query:

Measure% = 
VAR SelectedDistrict = SELECTEDVALUE('State Data'[District])
VAR SelectedState = SELECTEDVALUE('State Data'[State])
VAR TotalValue = SUM('State Data'[Value])
VAR TotalValueDistrict = CALCULATE(SUM('State Data'[Value]),ALL('State Data'[District]))
VAR TotalValueState = CALCULATE(SUM('State Data'[Value]),ALL('State Data'[State]))
VAR StatePercentage = FORMAT(DIVIDE(TotalValue,TotalValueState),"Percent")
VAR DistrictPercentage = FORMAT(DIVIDE(TotalValue,TotalValueDistrict),"Percent")
RETURN
IF(ISBLANK(SelectedDistrict) && ISBLANK(SelectedState),TotalValue,IF(ISBLANK(SelectedDistrict),StatePercentage,DistrictPercentage))

Hope this helps.