Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
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 ?
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.
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.
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
@Anonymous
What if you use DESC in the TOPN formula
Winning party votes (TOPN) = SUMX ( TOPN ( 1, Parti, Parti[Parti], DESC ), [Number of votes] )
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |