DAX TopN with Others not abiding to Filters

[Anonymous]

Hello Everyone!

I have recentely started relearning DAX for a project, after doing a course over a year ago.
I am trying to make a Pie chart that displays TOPN Tasks, and a slice for others.

After quite some struggeling i got a code working based on this guide https://goodly.co.in/top-n-and-others-power-bi/ 

I have a Table with every unique Task called "Pseudo Prod Table" and a row with "Other". And the code: 

TOP N =

VAR TopTask = TOPN(3, ALLSELECTED('Pseudo Prod Table'), Calculate(SUM(Data[Uur gedaan])))

VAR TopHour = Calculate(SUM(Data[Uur gedaan]),KEEPFILTERS(TopTask))

VAR LowHour = Calculate(SUM(Data[Uur gedaan]), ALLSELECTED('Pseudo Prod Table'))-CALCULATE(SUM(Data[Uur gedaan]),TopTask)

VAR CurrentTask = SELECTEDVALUE('Pseudo Prod Table'[Taaknaam Niv 2])

Return IF(CurrentTask <> "Other", TopHour, LowHour)

And this code works! I get a piechart with 3 tasks and a "Other" slice.

But every time i try to apply any filter on the data, the "Other" slice goes away. When i display VAR LowHour to a Card it works fine with the Filtering. But the "Other" row just doesnt want to stay after filtering. 

Does anyone know how i can make it stay with Filters? 

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

This is a dynamic filter, unless there is such a calculated column in the table (with the values 1, 2, 3, xxx and others). But there is another problem, the sorting is static and cannot be changed as the slicer options change...

Pie Chart - Top 5 - Rest named Other

Filter top 3 products with a row for others using DAX in Power BI

DAX Fridays! #132: Dynamic TopN + Others in Power BI

Best Regards

[Anonymous]

I was worried that might happen. Weird that the link did have a filter for year and it worked fine.

I tried the two videos you send with dynamic other ideas. But they both seem to assume that the data table has a single value per Product that you can rank.

My table is a big blob of data and every product appears hunderds of times. When i try to Rank them i just get the message

"A single value for Column 'Uur Gedaan' in table "Data" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation".

But when i put in SUM, everything gets rank 1, instead of its proper rank. 

[v-yiruan-msft]

Hi @Anonymous ,

It seems you can't get the correct ranks using current measure... Please refer the following links which has the similar problem as yours, hope they can help you get the expected result.

RANKX returning all ranks as 1

CALCULATE is needed to perform the context transition for each row of the table

otherwise the filter context is always the same and all items will rank 1

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	CALCULATE ( SUM('Weekly Summary'[Impressions]) ),
	,
	ASC
) 

However if you already have a Measure for SUM( 'Weekly Summary'[Impressions] ) then you don't need the CALCULATE

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	[MEASURE],
	,
	ASC
) 

---

RANKX does not give the correct result

In addition, you can refer the following blog to create a measure for getting the ranks.

Use of RANKX in Power BI measures

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

@v-yiruan-msft Many thanks for you help. I have gotten it working with the ranking fix and with the video from SQL BI. You are incredible 🙂

[Anonymous]

Also Hai @v-yiruan-msft. Still getting used to this forum 🙂