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.
Dear team,
I am trying to create a Top/Bottom N list of sales by customers for which [Sales Total] are between a dynamic range of values chosen by the user.
For this I would like to use the in-built Visual filter on measure in the Filter panel.
To achieve this I have created Rank measure which will dynamically switch from Descending to Ascending ranking order depending on the value of SLICER_RankingOrder (Top or Bottom).
Rank Customer = IF (NOT(ISBLANK([Sales Total])), SWITCH ( SELECTEDVALUE(SLICER_RankingOrder[SLICER_RankingOrder],"No value"), "Top", RANKX(ALLSELECTED(FACT_Sales[Customer]), [Sales Total], ,DESC), "Bottom", RANKX(ALLSELECTED(FACT_Sales[Customer]), [Sales Total], ,ASC), RANKX(ALL(FACT_Sales[Customer]), [Sales Total], ,DESC)), BLANK() )
And from there another measure Sales Top N to blank out any [Sales Total] which are beyond the N rank.
Sales Top N = IF ([Rank Customer] <= PARAM_RankingElements[RankingElements Value], [Sales Total], BLANK() )
So far so good.
Now when I apply the Visual filter on Total Sales measure, it seems to be applied on top of the ALLSELECTED customer context.
As a result I will get something like this (red). While the result I am looking for is in blue.
Is there a way to work around this?
Note: I found an alternate way using What-if parameters and integrating them in a calculated measure which I will then rank.
The result is fine but in terms of performance when I run this on my production dataset of 1M+ rows, it takes each time 5-10s to run. That's why I am exploring Visual filter on measure which seems to be giving much better performance results.
Thanks in advance for your help.
Solved! Go to Solution.
Hi @Maikeru,
I thought hard and found the measure doesn't change the context but the values. So I would suggest you add a calculated column to the table 'FACT_Sales'. Then it would be easy now.
TotalSales = CALCULATE ( SUM ( FACT_Sales[Sales] ), ALLEXCEPT ( FACT_Sales, FACT_Sales[Customer] ) )
Best Regards,
Dale
Hi @Maikeru,
I thought hard and found the measure doesn't change the context but the values. So I would suggest you add a calculated column to the table 'FACT_Sales'. Then it would be easy now.
TotalSales = CALCULATE ( SUM ( FACT_Sales[Sales] ), ALLEXCEPT ( FACT_Sales, FACT_Sales[Customer] ) )
Best Regards,
Dale
Thank you Dale! It works like a charm!
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |