Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
So, i am trying to create a custom filter that enables us to filter our products by their rank in regards to the revenue.
The measures used are (Refer image below for more clarity):-
Solved! Go to Solution.
Hi, @Anonymous
In my opinioin,
FILTER is an iteration function and it calculates row by row. So, inside the measure, the calculation is considering PRODUCT by PRODUCT from the perspective of ranking -> when the ranking criteria for one product meets, then shows the result row by row -> when the ranking criteria for one product does not meet then no-show.
IF function sees the result of each row. So, when seeing product by product, it seems it is the same as filter function. However, when coming to the last line, which is the TOTAL, if function sees the total amount then checks whether it meets the criteria. So, if you select the top 10, it works because 139,708 (which is top 1) is within the top10 criteria. However, when you select 11~20, it shows blank because you wrote inside the measure that if the rank (which is TOP1 in this case) is not in between 11 and 20 then blank.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Anonymous
In my opinioin,
FILTER is an iteration function and it calculates row by row. So, inside the measure, the calculation is considering PRODUCT by PRODUCT from the perspective of ranking -> when the ranking criteria for one product meets, then shows the result row by row -> when the ranking criteria for one product does not meet then no-show.
IF function sees the result of each row. So, when seeing product by product, it seems it is the same as filter function. However, when coming to the last line, which is the TOTAL, if function sees the total amount then checks whether it meets the criteria. So, if you select the top 10, it works because 139,708 (which is top 1) is within the top10 criteria. However, when you select 11~20, it shows blank because you wrote inside the measure that if the rank (which is TOP1 in this case) is not in between 11 and 20 then blank.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@mahoneypat You are right, when i select top 10, i do see values- but why is it that with measure number 4- other TopN categories do not work. Even with Top 10- the subtotal is giving the full total for all categories combined not for top 10.
I reproduced your scenario on a similar model (please provide a link to a pbix if not), and this measure seems to do the trick.
Dynamic TopN =
VAR minRank =
MIN ( TopNTable[Min] )
VAR maxRank =
MAX ( TopNTable[Max] )
VAR TOPNcount = maxRank - minRank + 1
VAR ProductValues =
VALUES ( 'Product'[Product] )
VAR SalesTable =
ADDCOLUMNS ( ALL ( 'Product'[Product] ), "cSales", [Total Sales] )
VAR TOPNX2 =
TOPN ( TOPNcount, TOPN ( maxRank, SalesTable, [cSales], DESC ), [cSales], ASC )
RETURN
SUMX ( FILTER ( TOPNX2, 'Product'[Product] IN ProductValues ), [cSales] )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Do you see a Total value when "Top10" is selected? Your total is likely returning as 1, so it is excluded by your filter in #4. A couple other suggestions:
1. You should leverage variables in these expressions (even create a variable within the FILTER of #3) to potentially improve performance (i.e., calculate your M_Rank and your Min/Max values once).
2. Probably won't be any better (after you put in variables) but you could also consider an expression with KEEPFILTERS and nested TOPNs (using min and max slicer values, one with ascending, one with descending).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Hello Sir, At first i did not understand when you responded "Your total is likely returning as 1, so it is excluded by your filter in #4". But now with more explanation from @Jihwan_Kim - I do appreciate your point. Thank you both for helping me improve my understanding of Power BI.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |