Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

DAX Help

What if we need to display Top N/Bottom N products and regions both. It should show only top 5 or bottom 5 products and regions. Only 5 row table.   Following measure works for only products.   ...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    Hi Anonymous,

    Thank you for reaching out to the Microsoft fabric community forum.  I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

    See there is No relationship between Products and Regions:


    Outcome:




    I am also including .pbix file for your better understanding, please have a look into it:

    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

    Thank you for using Microsoft Community Forum.

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

Anonymous To display the top 5 or bottom 5 products and regions, you need to modify your measure to account for both products and regions.

 

dynamic time period with top rank =
VAR N = SELECTEDVALUE('Top N Value'[Top N Value])
VAR RankType = SELECTEDVALUE('TopBottom Selector'[RankType])
VAR IsTop = RankType = "Top"
VAR IsBottom = RankType = "Bottom"
VAR BaseMeasure = [Dyanamic Time Period Measure]

// Create a table of products and regions with non-blank measure values
VAR CombinedTable =
FILTER(
CROSSJOIN(ALLSELECTED(Products[ProductName]), ALLSELECTED(Regions[RegionName])),
NOT ISBLANK(CALCULATE([Dyanamic Time Period Measure]))
)

// Rank combined products and regions based on the selected measure
VAR CombinedRankTop =
RANKX(
CombinedTable,
CALCULATE([Dyanamic Time Period Measure]),
,
DESC
)

VAR CombinedRankBottom =
RANKX(
CombinedTable,
CALCULATE([Dyanamic Time Period Measure]),
,
ASC
)

RETURN
SWITCH(
TRUE(),
IsTop && CombinedRankTop <= N, BaseMeasure,
IsBottom && CombinedRankBottom <= N, BaseMeasure,
BLANK()
)

 

You may need to ensure that your Selected Measure top N rank measure is correctly integrated with this new logic.

 

Selected Measure top N rank =
SWITCH(
SELECTEDVALUE('Measure Selector'[Measure Name]),
"Sales", [dynamic time period with top rank],
"Quantity", [dynamic time period with top rank],
"GP", [dynamic time period with top rank]
)

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    This is complete different measure

    Selected Measure top N rank =
    SWITCH(
        SELECTEDVALUE('Measure Selector'[Measure Name]),
        "Sales", [Total Sales],
        "Quantity", [Total Quantity],
        "GP", [Total GP]
    )
     
    Please fix code as its not working