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
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?

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

      Hi,

      Please share the link of the pbix file.

      You can try to use onedrive, googledrive, or dropbox to share the link.

      Thanks.

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

    This is what i see, How do i add an .pbix file to this message?  If i drag it on, it says file not compatible?