Forum Discussion

PPStar's avatar
PPStar
Icon for Helper V rankHelper V
3 years ago

top 5 VALUES by month

Hi 

i have the following data

 

Usage |  Month Year | Name

123     | Jan 22           | Item A

124     | Jan 22           | Item B

14412 | Jan 22           | Item C

1324   | Feb 22          | Item A

12       | Feb 22         | Item B

9242   | Feb 22         | Item D

 

and so fourth. 

 

The data is basically showing me the usage of all items by month. I have over 500 items each item is used every month. 

 

I need to create a graph which shows me the the top 5 items used each month. 

 

I have been using the TOP N tool in the filter pane, but something tells me this is not right, reason is in for e.g. Item F is used heavily in Jan 22, but not used in Feb 22, when i plot my graph, item F is not shown. Not sure why

 

I would like to do this via DAX. 

 

I tried to do something like 

RankX = RANKX(ALL('ItemTable'),[Total Usage]) , this however returns something like
 
Item Table contains a list of all my item. 
Total Usage is a measure whic goes to the usage table and does a calcualtes the usage via the Count Rows (
COUNTROWS(UsageTable))
 
Month Year | Total Usage | Item Name | RankX 
Jan 22         | 1                  | Item A         | 344
Jan 22         | 1                  | Item B         | 344
Feb 22         | 1                  | Item B         | 342
 
Totaly  new to RANKX function, any help will be appreciated. 
 
Thanks
Need Help

9 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

     

    Top 5 item usage by month: =
CALCULATE (
    SUM ( Data[Usage] ),
    KEEPFILTERS (
        TOPN ( 5, ALL ( 'Item'[Item] ), CALCULATE ( SUM ( Data[Usage] ) ), DESC )
    )
)

     

    • PPStar's avatar
      PPStar
      Icon for Helper V rankHelper V
      3 years ago

      I cant get it to work

      My relationships are defined as below

       

      Items Table has a 1:M relationship with Usage Table.  i.e. One ID from the Items table can have many items in the usage table. 

      The Usage Table has a 1:M relationship with the dates table. I.e.creation time on the usage table is a Many and it that has 1 relationship with the date table.  i.e see below

       

      I noticed in your formulae you are doing the sum of the data usage, I already have this value stored as a measure, so i am doing 

      top5 = CALCULATE([TotalUsage],KEEPFILTERS(TOPN(5,ALL(Items[ItemId]),CALCULATE([TotalUsage]),DESC)))
       
      I just get back 0 every time. 
       
      What am i doing wrong?
      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        Thank you for your message.

        Please check if Items table has other columns that influence the measure [TotalUsage] in the visualization.

        Once the columns are identified, please write the measure something like below.

         

        Top 5 item usage by month: = 
CALCULATE (
    [Usage measure:],
    KEEPFILTERS (
        TOPN ( 5, 
ALL ( 'Item'[Item],'Item'[columnAinthevisualization],'Item'[columnBinthevisualization], and so forth  ),  [Usage measure:], DESC )
    )
)

         

        Or, if it is OK with you, please share your sample pbix file's link and then I can try to look into it.

        Thanks.