Forum Discussion

slazarov's avatar
slazarov
New Member
10 years ago
Solved

Ranking items from multiple periods

Hello everyone,   Yet another ranking question but I seem to be stuck. Basically, I have a table consisting of a productid, year, month and sales. It looks like this:   As you can see each pr...
Need Help
  • HarrisMalik's avatar
    HarrisMalik
    10 years ago

    you can try two things:

     

    1. Create a calculated measure called Total Sales = Sum(Sales)

    2. For ranking Measure use following expression:

     

    Rank = RANKX(ALL(Table[ProductID],[Total Sales])

     

    this will rank Each product for each month on basis of sales. You can use the new Rank measure in your visual filters and filter on topN. I think the calculated measures cannot be used as slicers so you have to use it as filter (Please someone correct me if I am wrong)

     

    Regards

    Harris

HarrisMalik's avatar
HarrisMalik
Continued Contributor
10 years ago

you can try two things:

 

1. Create a calculated measure called Total Sales = Sum(Sales)

2. For ranking Measure use following expression:

 

Rank = RANKX(ALL(Table[ProductID],[Total Sales])

 

this will rank Each product for each month on basis of sales. You can use the new Rank measure in your visual filters and filter on topN. I think the calculated measures cannot be used as slicers so you have to use it as filter (Please someone correct me if I am wrong)

 

Regards

Harris

  • Bjoern's avatar
    Bjoern
    Continued Contributor
    10 years ago

    HarrisMalik You can definitely use calculated colums als slicers. I do it quite often, if I have categorized data.

     

    I see a problem that you can not filter for top10 in visuals, but only apply a "sort by". You have to filter based on the rank in the filter of the respective visual:

     

    See also:

    http://community.powerbi.com/t5/Desktop/Show-top-n-entries/td-p/1490

    • HarrisMalik's avatar
      HarrisMalik
      Continued Contributor
      10 years ago

      Bjoern Ofcourse you can use calculated columns in the slicers but I am taking about calculated measures. Did you use caluclated measure as slicer?

       

      Cheers

      Harris

      • Bjoern's avatar
        Bjoern
        Continued Contributor
        10 years ago

        HarrisMalik Uuups ... you are right, sorry. I misread your post, as I either say "calculated columns" or "measures", as in AS Tabular.

         

        My assumption:

        I am not sure if it would be possible to use measures as slicers, as they aggregate dynamically on the basis of the other selections and return the values. Only a single value is returned for our case (for all rows), so it would not make any sense.