How to calculate the sales of the top N in a dataset?

[Anonymous]

Hello,

Within PowerBi, is there a way to caculate the total sales for the Top N of a dataset? I would like to create a calculation that says, for example, that the top 10 products sold make up x% of total sales. 

Regards,

Alex

[v-shex-msft]

Hi @Anonymous,

Please share some sample data with same data structure for test to coding formula.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

Not sure if this is the most efficient approach, but I got it up and running. I used sample data from the PowerBI Dashboard in a Day assets. The assumption with this solution is that you leverage some sort of Sales fact table, as well as a Product dimension table. 

1. Since there is one row per product in the dimension, my first step was to add a calculated column to the Product dimension that represents the total revenue for each product:

Revenue = SUMX(FILTER(Sales,'Product'[ProductID] = Sales[ProductID]), Sales[Revenue])

2. I then created another calculated column to represent the rank of products by revenue:

Rank = RANKX('Product','Product'[Revenue])

3. At that point, you can just create a calculated measure for your % calc:

Top 10 % = [Top 10 Revenue] / SUM(Sales[Revenue])

I'm sure there's a way to do this all within the same fact table, but I just had the thought to create new columns in the dimension since it was quick. I attached an image below demonstrating the results. Let me know if this helps.

Ben