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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Top N of sum of column per name per date

Let's say I have three columns:

 

NAME: Name of personel

SALES: Amount to be summed

DATE: date

 

I can not manage to make a top 10 of NAMEs that have the highest SALES per DATE

 

For example if this is data:

 

NameDateSales
A160
A1150
B3225
C2

150

D5

125

A2

100

 

A top 3 should look like this:

 

NameDateSales
B3225
A1210
C2150

 

I can make a table sorted by Sales of above three columns showing all data with correct sum of sales per date per name, however once I filter that visual by Name -> Top N - > top 10 by Sales, I get complete gibberish. How do I go about this?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try a new measure like this and filter for <=3 

rankx(allselected(Table[name], table[date]), calculate(sum(table[sales])))

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
ttps://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

 

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

OK so this is weird. Your solution DOES work, but not yet in the way I want it to. 

 

The top 10 is made from data from table A.

Table A contains a date column, which contains weirdly formatted dates that I don''t use anywhere in the report.

Table A is linked to column B through a Transaction ID (each transaction ID = one row that also contains name and sales amount). Column B has the correct date format for each transaction ID. The date format in column B is linked to a Date Table (dates, weeks, months, years) that I use throughout the report and seems to work correctly when summarizing data from Table A.

 

However... when I use the date column from the Date Table in my Top N table, I get gibberish values. If I use the date column from Table A, I get a correct top 10 table. Even though the date translation (date, weeks, months, etc.) seems to work correctly for all other visualizations that use data from Table A?

 

How do I make the table with the correct dates from the Date Table?

 

EDIT: I'll mark this as solved as a solution was given for the original question. I'll post the new question in a new topic.

 

amitchandak
Super User
Super User

@Anonymous , Can you share a bigger sample. I tried the measure I shared above and got the results

 

amitchandak_0-1607995188524.png

 

amitchandak
Super User
Super User

@Anonymous , Try a new measure like this and filter for <=3 

rankx(allselected(Table[name], table[date]), calculate(sum(table[sales])))

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
ttps://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

 

 

Anonymous
Not applicable

Thank you for the quick reply. I've seen this posted and tried it, but this doesn't give the desired result.

 

If I just make the table (no top N, no fancy filtering), the top 10 of sales per date per employee (in a very very large table) show the correct numbers. There are no duplicates of values in the top 10 of that very large table. However once I try your solution to make a actual top 10 list, the highest value is not on top anymore. There is a whole bunch of values that have ranking "1", i.e. I end up with a much larger list than a Top 10, even though there are no duplicate values in the actual top 10 ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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