Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I would like to calculate the TOP 20 sale percentage of total sale by different store
My table have the column as below
Table Name: Top item sale
1. Store
2. item no
3. Net sale
How can I get a table like below:
Store A : 20%
Store B : 30%
Store C : 15%
Thank you very much
Leo Lam
Solved! Go to Solution.
Hi,
Try these measures
Total sale = SUM(Data[Net sale])
Total sale of Top 20 items = SUMX(TOPN(20,VALUES(Data[Item No]),[Total sale]),[Total sale])
Total sale of Top 20 items (%) = [Total sale of Top 20 items]/[Total sale]
Hope this helps.
@leolam1234 a little bit hard to visualise without data but say if you had..
Set up a slicer for store identifier (A,B,C)
Then have a table
Column 1: Store identifier (A,B or C)
Column 2: Item Number
Column 3: Net Sale
Click on the table, go into visual level filters, click on Store Identifier, Filter type drop down: "top N", put 20 into the show items, and move "Net Sale" field into the "By value".
Then when you apply your slicer for each store identifier, you will be able to see the associated item number and corresponding net sale for the top 20 of sales by each store.
Hi Chris,
Thank you for your help,
Actually i would like to get the percentage of the TOP20 to the total sale.
Do you have any idea?
Thanks
Hi,
Try these measures
Total sale = SUM(Data[Net sale])
Total sale of Top 20 items = SUMX(TOPN(20,VALUES(Data[Item No]),[Total sale]),[Total sale])
Total sale of Top 20 items (%) = [Total sale of Top 20 items]/[Total sale]
Hope this helps.
it works, thanks a lot
You are welcome.
Hi @leolam1234 ,
According to your description, my understand is that you want to calculate the TOP 20 sale percentage of total sale by different store, the data is stored in table "Top item sale".
Please use the following DAX query:
Measure = VAR a = CALCULATE ( SUM ( 'Top item sale'[Net sale] ), TOPN ( 5, 'Top item sale', 'Top item sale'[Net sale], DESC ) ) VAR b = CALCULATE ( SUM ( 'Top item sale'[Net sale] ) ) RETURN a / b
Best Regards,
Teige
Hi,
Thanks for your reply
Sorry that i have date field in the table, as i want to combine the sale with different date.
For the method, it may show the Top sale with one date not summarized data
Thanks
Leo
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |