Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
PPStar
Helper V
Helper V

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
9 REPLIES 9
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1674127328754.png

 

Jihwan_Kim_1-1674127611065.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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

 

PPStar_0-1674132844038.png

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?

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

PPStar_0-1674139526589.png

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?

 

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

Hi,

Please share the link of the pbix file.

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

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

https://drive.google.com/file/d/1E1BQCz6HY3xgmUo3LePYUG8OarQG1M7y/view?usp=sharing

 

can you access the file via that link?

Note

Consumer Usage is the measure i am using. We only want to see Consumer Usage of items. Therefore the consumer usage is te metric we are measuring against. So we want to see top 5 consumer used item by Month. 

 

Thanks

Hi,

Thank you for sharing.

However, it is difficult for me to understand the data model.

- Where can I find consumers information?

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

- Items table consists of item and each item has a ID

- Audit consists of the audit history of that item such as which operations they do to that item, (view it, edit it, export it etc), it also contains the ID of the item and the date that audit of that item took place. 

- Activities is seperate table i created to define if an operation is a consumer activity or a admin activity.  It connected to the audit table, so if the audit history table says the item was viewed, then i can state that was a consumer activity. If the audit table says that item was exported, then i know it was an admin activity. 

 

The consumer is the operation they did from the audit history table labeled as a role. 

 

Hope that makes sense

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.