DAX for Bottom N - with slicer (what if parameter)

[TRK]

Sample Data:

Category            Type                Group              Commodity     Baseline Value
Category 1      Environment       Activity 8                  Food               0
Category 2       Economic            Activity 1           Non-Food            0
Category 3       Animal                Activity 6           Organic                0
Category 2       Animal               Activity 7            Non-Food         22553.43
Category 2       Animal               Activity 9             Non-Food        225534.26
Category 1       Economic           Activity 2           Food                 7245000
Category 1       People                 Activity 4            Food            34802757

I created a parameter and use that to calculate TopN using this parameter slicer.  I have a visual with commodity - and then filters with the other columns.  This works.   If select top 4 in TOPN slicer - it shows top 4 commodities, if filter by any other column - it shows the top 4.

I used the following measures: 

Total Baseline = SUM('1 - BL Baseline'[Baseline Value])

TOP N Baseline = GENERATESERIES(0, 60, 1)

TOP N Baseline selectedValue = SELECTEDVALUE('TOP N Baseline'[TOP N Baseline])

Total Baseline Top N (Commodities) =

VAR SelectedTop = SELECTEDVALUE('TOP N Baseline'[TOP N Baseline])

RETURN

SWITCH(TRUE(),

SelectedTop = 0, [Total Baseline],

RANKX (

ALLSELECTED( 'Baseline'[Commodity] ), [Total Baseline])

<= SelectedTop, [Total Baseline])

For Bottom N:  since the above automatically seems to sort from largest to smallest - I would need to have the formula sort from smallest to largest.  I tried adding ASC in the Allselected section of formula:

Bottom N Baseline = GENERATESERIES(0, 60, 1)

Bottom N Baseline selectedValue = SELECTEDVALUE('Bottom N Baseline'[Bottom N Baseline])

Total Baseline Bottom N (Commodities) =

VAR SelectedBottom = SELECTEDVALUE('Bottom N Baseline'[Bottom N Baseline])

RETURN

SWITCH(TRUE(),

SelectedBottom = 0, [Total Baseline],

RANKX (

ALLSELECTED( 'Baseline'[Commodity] ), [Total Baseline],,ASC, Dense)

<= Selectedbottom, [Total Baseline])

However, that did not work quite right.  It appears to, when only have slicer for Bottom N - set for say 3 - it shows the bottom 3 commodities in the visual.  However, when I slice by category - 1 of the categories does show bottom 3, but the others show the bottom 2 only.  In a table view - there seems to be more than 3 valuse returning for this Bottom N Baseline.

How can I modify the DAX so that I get Bottom N with the fuctionality of the Bottom N slicer and still use all my other filters?

[v-zhenbw-msft]

Hi @TRK ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft · Accepted solution]

Hi @TRK ,

Do you want to show the bottom N with slicer like this,

When select bottom 4,

If yes, you can refer our steps.

1. In Editor Query, sort the Baseline value by ASC and add an index column.

2. We can create a new parameter as Bottom Top N. Then we need to change the Total Baseline to this,

Total Baseline = SUM('Table'[Baseline Value]) + SUM('Table'[Index])*0.00001

3. At last we can create a Bottom Top N measure.

Total Baseline Bottom N (Commodities) = 
SWITCH(
    TRUE(),
    [Bottom N Baseline Value] = 0, [Total Baseline],
    RANKX (ALLSELECTED( 'Table' ), [Total Baseline],,ASC,Dense)<= [Bottom N Baseline Value], [Total Baseline])

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@TRK , refer if this example of this topic can help : https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[TRK]

Hi,  yes - I used this post for TOP N - as you can see it is the same DAX.

However  - I am trying to do the same thing with BOTTOM N - and cannot get that to work (see my original post).  

How do I get  bottom N  - as Rankx auto sorts from highest to lowest.