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
datajunkie_29
Helper I
Helper I

Top/Bottom N of a Measure by Slicer

Hello All,

 

I have a table visual as shown below and all the slicers above:

datajunkie_29_0-1685660020053.png

How can I create a dynamic measure that shows the Top/Bottom 10 by MarginLost based on the slicer selections as shown in the visual above.

datajunkie_29_0-1685660932615.png

 

eg: if I select a region it should show Top 10  for region or if branch level/any slicer is selected it should do the same.

 

Any help is appreciated! Thank you. 

5 REPLIES 5
some_bih
Super User
Super User

Hi @datajunkie_29 Please share some details. Measure definition

[M_Sum_MargingLost],

and usage in VAR 's could be easly replaced with Sales. Did you do it or...?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

Hi @datajunkie_29 as you wrote "Top/Bottom 10 by MarginLost based on the slicer selections",  as one of possible solutions, I created:

1. Measure for MarginLost as simple sum; 

M_Sum_MargingLost = sum(Sales[MarginLost])
2. Two measures for Top and Bottom based on M_Sum_MargingLost:
2.A For Top:
M_test_TopDyn =
VAR _number_of_values=10    --insert value for top
VAR _ranking =
    RANKX(
        ALLSELECTED(Sales),
        [M_Sum_MargingLost],--choosing Measure
        ,
    desc,                   --choosing Top as desceding
    Dense                   --dense original
    )
VAR _Result=
IF(_ranking<=_number_of_values,_ranking,BLANK())
RETURN _Result
 
2.B For Bottom:
M_test_BottomDyn MarginLost =
VAR _number_of_values=10    --insert value for top
VAR _ranking =
    RANKX(
        ALLSELECTED(Sales),
        [M_Sum_MargingLost],--choosing Measure
        ,
    ASC,                   --choosing Bottom as ascedin
    Dense                  
    )
VAR _Result=
IF(_ranking<=_number_of_values,_ranking,BLANK())
RETURN _Result
 
Please aware that in you file there are very few data to test 10 values.
If this helps please kudo.
If this solves your problem please accept it as a solution.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hello @some_bih,

 

Unfortunately it didn't resolve as all the values in the rank were 1 if I did it by Sales. 

some_bih
Super User
Super User

Hi @datajunkie_29  if possible, your file will be needed.   





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hello @some_bih ,

 

Here is the sample data file for reference with very limited data. 

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.

Top Solution Authors