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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors