TOP N and Others in several subcategories

Anonymous — Tue, 03 Sep 2024 14:12:08 GMT

Hello guys,

I have organizational hierarchy table, which contains 3 levels: path_5, path_6 and ziskatel.

Then I have data table, where is id of lowest level of hierarchy (id_ziskatel).

I have created meassure, which calculate cumulative sum of profit.

12M príjem zo štruktúry =

VAR EndDate = MAX(tbl_kalendar[datum])

VAR StartDate = EDATE(EndDate,-12)+1

VAR Result = CALCULATE(

SUM(data[prov]),

DATESBETWEEN(tbl_kalendar[datum],StartDate,EndDate),

data[id_prijem]=2)

return

Result

Afterall, I want to display in matrix table visualization in Power BI TOP 10 subject according to "12M príjem zo štruktúry" and others summarize into subject "Others".

I tried code:

TOP N =

VAR Table_TOP10_ziskatel = TOPN(10,

ALLSELECTED(tbl_rebrik[ziskatel]),

[12M príjem zo štruktúry])

VAR TOP10_ziskatel = CALCULATE(

[12M príjem zo štruktúry],

KEEPFILTERS(Table_TOP10_ziskatel))

VAR Others_ziskatel = CALCULATE(

[12M príjem zo štruktúry],

ALLSELECTED(tbl_rebrik[ziskatel]))

CALCULATE([12M príjem zo štruktúry],

Table_TOP10_ziskatel)

VAR Current_Product_ziskatel = SELECTEDVALUE(tbl_rebrik[ziskatel])

VAR Result_1 = IF(Current_Product_ziskatel<>"Ostatní",TOP10_ziskatel,Others_ziskatel)

VAR Table_TOP10_path_6 = TOPN(10,

ALLSELECTED(tbl_rebrik[path_6]),

[12M príjem zo štruktúry])

VAR TOP10_path_6 = CALCULATE(

[12M príjem zo štruktúry],

KEEPFILTERS(Table_TOP10_path_6))

VAR Others_path_6 = CALCULATE(

[12M príjem zo štruktúry],

ALLSELECTED(tbl_rebrik[path_6]))

CALCULATE([12M príjem zo štruktúry],

Table_TOP10_path_6)

VAR Current_Product_path_6 = SELECTEDVALUE(tbl_rebrik[path_6])

VAR Result_2 = IF(Current_Product_path_6<>"Ostatní",TOP10_path_6,Others_path_6)

VAR Table_TOP10_path_5 = TOPN(10,

ALLSELECTED(tbl_rebrik[path_5]),

[12M príjem zo štruktúry])

VAR TOP10_path_5 = CALCULATE(

[12M príjem zo štruktúry],

KEEPFILTERS(Table_TOP10_path_5))

VAR Others_path_5 = CALCULATE(

[12M príjem zo štruktúry],

ALLSELECTED(tbl_rebrik[path_5]))

CALCULATE([12M príjem zo štruktúry],

Table_TOP10_path_5)

VAR Current_Product_path_5 = SELECTEDVALUE(tbl_rebrik[path_5])

VAR Result_3 = IF(Current_Product_path_5<>"Ostatní",TOP10_path_5,Others_path_5)

VAR Result = SWITCH(

TRUE(),

ISINSCOPE(tbl_rebrik[ziskatel]) && ISINSCOPE(tbl_rebrik[path_6]) && ISINSCOPE(tbl_rebrik[path_5]),Result_1,

ISINSCOPE(tbl_rebrik[path_6]) && ISINSCOPE(tbl_rebrik[path_5]),Result_2,

ISINSCOPE(tbl_rebrik[path_5]),Result_3)

return

Result

But It works only for first level of hierarchy "path_5". If I drilldown to path_6, I will get just TOP 10 in path_6, but not the subject others ("Ostatní").

Can you please help me? Also I would like to rank those TOP 10 subjects according to amount descending and the others puts in the end.

Thank you.

Re: TOP N and Others in several subcategories

Anonymous — Mon, 02 Sep 2024 09:11:11 GMT

Hi Anonymous ,

I wasn't able to reproduce it using the information you provided.

Could you share the pbix file containing the relevant data for me to test further?

Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,

Dengliang Li

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

Re: TOP N and Others in several subcategories

Anonymous — Tue, 10 Sep 2024 13:35:13 GMT

Hi,

I have created test version. You can download it on https://drive.google.com/file/d/1oi9B2TRLj8MUsB92x7lt6jPRLwD34fOQ/view?usp=drive_link.

Thanks.

Lucia

topic Re: TOP N and Others in several subcategories in DAX Commands and Tips

TOP N and Others in several subcategories

Re: TOP N and Others in several subcategories

Re: TOP N and Others in several subcategories