Forum Discussion

Koushikrish's avatar
Koushikrish
Helper I
4 years ago
Solved

DAX TOP N Measure on a Matrix Issue

Hello,   I have a requirement to display TOP 10 States on a Volume measure across a matrix visual. I have used the TOP N DAX Measure to achieve this :  CALCULATE([Volume], TOPN(10, ALL(dimGeograp...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Koushikrish glad it worked!

    To also remove the filter from Brand, this below might work (I've not tested this)

     

        CALCULATE(
        [Volume],
        TOPN(10,
            ALL(dimGeography[State_Code], dimGeography[RegionName]),
            CALCULATE([Volume], ALL('dimBrand'[BrandName]))
            DESC
        ),
        VALUES(dimGeography[State_Code])
    )

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Koushikrish , how should the visual look when you have Region added? Could you post the desired output, please.

Koushikrish's avatar
Koushikrish
Helper I
4 years ago

Hello Anonymous. These are the top 10 states. My desired output is to retain these Top 10 states only across columns even if I include anything on the rows.

Top 10 states:

This is how I need it when I include Region Name on the columns : 

 

 

 

But right now it shows this way when I include Region Name, its displaying the Top 10 states for each Region.

Hope that info was sufficient. Else please let me know.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Koushikrish I tried with some sample data but couldn't reproduce the first image in your firdt post.

    I think that perhaps that when the Region field is added, you need the measure remove the filters from the Region Field. I think this will do it:

     

     

        CALCULATE(
        [Volume],
        TOPN(10,
            ALL(dimGeography[State_Code], dimGeography[RegionName]),
            [Volume],
            DESC
        ),
        VALUES(dimGeography[State_Code])
    )

     

     

    • Koushikrish's avatar
      Koushikrish
      Helper I
      4 years ago

      Anonymous : This worked great, thank you! I have another question though. Consider I have to include another column from another table on the Row (instead of Region Name). Is there a way to get rid of the filter without explicity specifiying the field name? 
      For example, I tried to use another column called Brand Family on the row but it won't work as filter is removed only on the Region Name. I also tried this but didn't work as it looks like arguments for ALL function must be from same table. 

       




      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Koushikrish glad it worked!

        To also remove the filter from Brand, this below might work (I've not tested this)

         

            CALCULATE(
        [Volume],
        TOPN(10,
            ALL(dimGeography[State_Code], dimGeography[RegionName]),
            CALCULATE([Volume], ALL('dimBrand'[BrandName]))
            DESC
        ),
        VALUES(dimGeography[State_Code])
    )