Calculate cumulative percentage by group

Anonymous — Thu, 10 Mar 2022 14:04:11 GMT

Hello guys,

I am just starting out with DAX and I wanted to get some idea on how to do the following. I have a single Table that records sales transcations for "Products". Each transcation has a "Sales_quantity" and "Price". Additionally, the table has a "%Sales" Column that records the percentage of sales of the transcation for the product as a whole, which I've already calculated. What I need is the blue column "Target" that calculates the cumulative %Sales of each Group ranked by Price but i have no idea how to use DAX to calculate this column. Can you help me out here?

Product	Sales_quantity	Price	%Sales	Target
A	2000	3	66,66%	100%
A	1000	2	33,33%	33,33%
B	2000	20	20%	50%
B	3000	18	30%	30%
B	5000	21	50%	100%

Re: Calculate cumulative percentage by group

johnt75 — Thu, 10 Mar 2022 15:23:02 GMT

Create a calculated column using the below

Target = 
var currentPrice = 'Table'[Price]
var totalSales = CALCULATE( SUM('Table'[Sales_quantity]), ALLEXCEPT('Table','Table'[Product]) )
var filteredSales = CALCULATE( SUM('Table'[Sales_quantity]), ALLEXCEPT('Table','Table'[Product]),'Table'[Price] <= currentPrice )
return DIVIDE( filteredSales, totalSales )

topic Calculate cumulative percentage by group in DAX Commands and Tips

Calculate cumulative percentage by group

Re: Calculate cumulative percentage by group