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          | ...
Need Help
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.

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

        i have a demo file. How do i attach it?