cummulative total of volume

[nicoenz]

Hi everyone, 

i have a table that details the products, when and where they were produced and the volume

Series	Product	Plant		Volume
2024	Product 1	Plant 1		333
2024	Product 8	Plant 1		100
2024	Product 3	Plant 2		533
2024	Product 7	Plant 1		133
2024	Product 10	Plant 2		67
2025	…	…		…
2024	Product 2	plant 1		300
2024	Product 10	Plant 1		33
2024	Product 6	Plant 2		333
2024	Product 7	Plant 2		267
2024	Product 8	Plant 2		200
2024	Product 9	Plant 2		133
2024	Product 1	Plant 2		667
2024	Product 2	plant 2		600
2024	Product 9	Plant 1		67
2024	Product 3	Plant 1		267
2024	Product 4	Plant 1		233
2024	Product 5	Plant 1		200
2024	Product 6	Plant 1		167
2024	Product 4	Plant 2		467
2024	Product 5	Plant 2		400

I want to create a visual that shows the products, the sum of volume (sorted descending) and the accumulated volume (like the one below). In the context i will also filter for "year" (let's say 2024)

Product	Vol final	Cumul Vol
Product 1	1,000	1,000
Product 2	900	1,900
Product 3	800	2,700
Product 4	700	3,400
Product 5	600	4,000
Product 6	500	4,500
Product 7	400	4,900
Product 8	300	5,200
Product 9	200	5,400
Product 10	100	5,500
	5,500	5,500

I can create the visual but I can't create the Cummulative measure in DAX. i've been crunching my brain without success. Can you please help me?

thanks in advance!!!!!

nico

[speedramps · Accepted solution]

Well done @johnt75.

Here is another varient with some comments ...

Cumulative = 
// create a temp table of volume total by product
VAR SummaryTable = 
    ADDCOLUMNS(
    ALLSELECTED( 'Table'[Product]),
    "@volume", CALCULATE(SUM('Table'[Volume]))
)
// get the current product total
VAR myvolume = SUM('Table'[Volume])

// filter the summary table >= the current
VAR mysubset = FILTER(SummaryTable, [@volume] >= myvolume)

RETURN 
// sum the values in the subset
SUMX(mysubset, [@volume])

[v-dineshya · Accepted solution]

Hi @nicoenz ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. Imported sample data into Power bi desktop. Please refer snap.

2.  Created Total volume measure "Total Volume" with below DAX code.

     Total Volume = SUM('Data'[Volume])

3.  Created Cumulative Volume Measure "Cumulative Volume"  with below DAX code.

Cumulative Volume =

VAR CurrentProduct = SELECTEDVALUE('Data'[Product])

RETURN

CALCULATE(

    [Total Volume],

    FILTER(

        ALLSELECTED('Data'[Product]),

        [Total Volume] >= CALCULATE([Total Volume], 'Data'[Product] = CurrentProduct)

    )

)

4. In table visual, Drag the Product, Total Volume, Cumulative Volume and Series fileds in visual. To filter the "2024" data, In visual pane mention the Series as "2024" as in snap. Please refer output snap and PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[speedramps · Accepted solution]

Well done @johnt75.

Here is another varient with some comments ...

Cumulative = 
// create a temp table of volume total by product
VAR SummaryTable = 
    ADDCOLUMNS(
    ALLSELECTED( 'Table'[Product]),
    "@volume", CALCULATE(SUM('Table'[Volume]))
)
// get the current product total
VAR myvolume = SUM('Table'[Volume])

// filter the summary table >= the current
VAR mysubset = FILTER(SummaryTable, [@volume] >= myvolume)

RETURN 
// sum the values in the subset
SUMX(mysubset, [@volume])

[nicoenz]

worked perfectly well. by not using the relation parameter in the WINDOW parameter.

Thanks for the help

[johnt75]

You can create a measure like

Cumulative Volume = 
VAR SummaryTable = ADDCOLUMNS(
    ALLSELECTED( 'Table'[Product] ),
    "@vol", [Vol] 
)
VAR Result = SUMX(
    WINDOW(
        1, ABS,
        0, REL,
        SummaryTable,
        ORDERBY( [@vol], DESC, 'Table'[Product], ASC )
    ),
    [@vol]
)
RETURN Result

[nicoenz]

The input table has duplicate values. Therefore I got an error message: "WINDOW's Relation parameter may have duplicate rows. This is not allowed".

But I understand the logic of your measure. Thank you very much!!!!!!