Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Consider the below table where we have Product Category & Product Subcategory and the corresponding Total Sales.
We need get only the Product Category & sub category which contributes the Top 50% of the Total Sales.
Step 1 - Calculate the Contribution % for each row. (i.e) = 167 / 656(Total Sales)
Step 2 - Calculate Cumulative sum of Contribution %
Step 3 - From Step 2, get only the records which is in the range of 0% <= x <= 50%
In the below example, only first two records will come. So the table visual should filter accordingly and show only first 2 records.
Product Category | Product Sub Category | Total Sales | Contribution % | Cumulative Sum % | Rank |
A | A1 | 167 | 25.46% (=167/656) | 0% | 1 |
A | A2 | 150 | 22.87% (=150/656) | 48.32% (=25.46%+22.87%) | 2 |
A | A3 | 139 | 21.19% (=139/656) | 69.51% (=48.32%+21.19%) | 3 |
A | A4 | 110 | 16.77% (=110/656) | 86.28% (=69.51+16.77%) | 4 |
A | A5 | 90 | 13.72% (=90/656) | 100.00% (=86.28%+13.72%) | 5 |
Total = 656 (=167+150+139+110+90) |
Please help me in achieving the above logic. Thanks in advance !!
Hello @MadhumithaV_26 You can use SUMX to avoid any error Try out the below DAX
Calculated Columns :-
i.e
This is not working for me. Total Sales is a measure in my use case. So I can't able to use sum or Earlier function on top of a measure. Can you help me solving this?
Thanks
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |