Forum Discussion
TOP N elements using Visual filter on measure
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.
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
2 Replies
- v-jiascu-msftMicrosoft Employee
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
- MaikeruHelper II
Thank you Dale! It works like a charm!