Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

TOPN not working as expected?

Hi,

 

I have a datamodel of the Danish national election 2011/15, and I'm trying to calculate the number of votes to the winning parti per municipality.

To achieve this I'm using a simple Top N, but the result is only correct for some municipalities, and I can't figure out why that is.

If I use a equivilent rankx measure then RankX returns the correct result.

 

Is there some filter context I'm missing?

 

PBIX file:
https://files.fm/u/2aghrjws#_

 

 

TOPN Measure:
Winning party votes (TOPN) = 
SUMX(
    TOPN( 1 ;
        Parti; Parti[Parti]   ;ASC);
        [Number of votes])

Ranking:
RANK = RANKX( All(Parti); [Number of votes] ; ;DESC )

RankX Measure:
Winning party votes (rankx) = 
CALCULATE( SUM( Valgdata[Votes] );
    FILTER(
        Parti ; [RANK] = 1))
        

 

4 REPLIES 4
quentin_vigne
Solution Sage
Solution Sage

Hi @Anonymous

 

I have your file and I see what you are trying to do, but I feel like you don't need calculated columns or measure, but using the Top-N (or N first) auto calculation from powerbi it will work. If you did'nt know about it you can find it where you use filtering for visual under 'Advanced filter' 

 

So you are trying to get the vote of the 1st parti right ? 

Anonymous
Not applicable

@quentin_vigne

 

The overall purpose of the TOPN measure is to color a Shape Map visual of Denmark, so it will select the legend color for the party that win in that specific municipality in 2011 / 2015.

 

I couldn't get that to work with the advanced filter, so I'm trying a different approach.

 

After posting the initial message, I figured out that the TOPN approach will always show me the TOP 1 in the current filter context, while RANKX (1) will only return a single value for the party that won.

 

For my purpose, the RANKX approach works much better, as the shape map visual is only receiving a single value, thus colouring correctly, as shown in the attached image.

 

However... this still doesn't explain why TOPN is returning a different value to the rankx method.

shape map.PNG

 

 

HI @Anonymous,

 

I think the difference between measures is due to topn function, it only return n row from original table.


So if your table contains similar records it only return top 1 row based on default sort order. Maybe you can try to manually setting sort order column and try again.

 

TOPN Function (DAX)

A table with the top N rows of table or an empty table if n_value is 0 (zero) or less. Rows are not necessarily sorted in any particular order. 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

What if you use DESC in the TOPN formula

 

Winning party votes (TOPN) =
SUMX ( TOPN ( 1, Parti, Parti[Parti], DESC ), [Number of votes] )

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.