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 - I have data for four years. I need a cumulative sum and cumulative percentage across years by sub-category.
sample data:
Sub-Category | Total Qty |
Binders | 5818 |
Paper | 5035 |
Furnishings | 3484 |
Phones | 3220 |
Storage | 3075 |
Accessories | 2911 |
Art | 2883 |
Chairs | 2295 |
Appliances | 1706 |
Labels | 1358 |
Tables | 1212 |
Envelopes | 891 |
Fasteners | 888 |
Bookcases | 837 |
Supplies | 643 |
Machines | 424 |
Copiers | 234 |
I need output like this:
Sub-Category | Sum of Quantity | Csum | Csum% |
Binders | 5818 | 5818 | 16% |
Paper | 5035 | 10853 | 29% |
Furnishings | 3484 | 14337 | 39% |
Phones | 3220 | 17557 | 48% |
Storage | 3075 | 20632 | 56% |
Accessories | 2911 | 23543 | 64% |
Art | 2883 | 26426 | 72% |
Chairs | 2295 | 28721 | 78% |
Appliances | 1706 | 30427 | 82% |
Labels | 1358 | 31785 | 86% |
Tables | 1212 | 32997 | 89% |
Envelopes | 891 | 33888 | 92% |
Fasteners | 888 | 34776 | 94% |
Bookcases | 837 | 35613 | 96% |
Supplies | 643 | 36256 | 98% |
Machines | 424 | 36680 | 99% |
Copiers | 234 | 36914 | 100% |
Any help of this highly appreciated.
Thanks,
Sanj
Solved! Go to Solution.
@sanjeev803 try following measures:
1st measure - create sum of qty
2nd measure - cummulative sum
3rd measure - create grand total of qty for %
4th measure - create %
Sum = SUM( Table2[Total Qty] ) Sum Cummulative = IF( HASONEVALUE( Table2[Sub-Category] ), SUMX( FILTER( ALL( Table2 ), Table2[Total Qty] >= MAX( [Total Qty] ) ), [Sum] ), [Sum] ) Sum Total = CALCULATE( [Sum], ALL( Table2[Sub-Category] ) ) Sum % = DIVIDE( [Sum Cummulative], [Sum Total] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Add an Index in Power Query Editor.
Create a measure as follows
CSum = CALCULATE(SUM(Table[Total Qty]), FILTER(ALL(Table), Table1[Index] <= MAX(Table[Index])))
Drag the measure on to the table visualisation twice.
The second one --> right-click in the Values well and choose 'Show value as -> Percent of grand total'
@sanjeev803 try following measures:
1st measure - create sum of qty
2nd measure - cummulative sum
3rd measure - create grand total of qty for %
4th measure - create %
Sum = SUM( Table2[Total Qty] ) Sum Cummulative = IF( HASONEVALUE( Table2[Sub-Category] ), SUMX( FILTER( ALL( Table2 ), Table2[Total Qty] >= MAX( [Total Qty] ) ), [Sum] ), [Sum] ) Sum Total = CALCULATE( [Sum], ALL( Table2[Sub-Category] ) ) Sum % = DIVIDE( [Sum Cummulative], [Sum Total] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |