Cumulative Total by number of SKU's

[vafratis]

Hello, 

I am new to DAX expressions but after a lot of search I could not find a solution to my problem.

I want to create a line graph with cululative cost. Specifically: 

For each month I have to calculate the total item value for each item (some items appear multiple times each month), sort by value from largest to smallest, and then calculate cumulative value. 

ie.  

Item count 1 - value 200

item count 2 - value 350 (200 + 150)

item count 3 - value 450 (200 + 150 + 100)

Any ides on how to do it?

My data is inventory values and is as follows.

thanks in advance

Month	Material ID	Value
01.2023	A1	100
01.2023	A2	150
01.2023	A2	200
01.2023	A3	50
02.2023	A4	10
02.2023	A3	50
02.2023	A2	160
02.2023	A1	80
03.2023	A5	20
03.2023	A2	50
03.2023	A3	120

[Ashish_Mathur · Accepted solution]

Hi,

I cannot understand your desired result.  Refer to my solution based on my understanding.  If thisisn't what youw ant, t hen show the expected result very clearly.

[Anonymous]

Hi @vafratis ,

Please have a try.

First, create a new table with the following DAX expression:

Table =
SUMMARIZE (
    'Inventory Values',
    'Inventory Values'[Month],
    'Inventory Values'[Material ID],
    "Total Value", SUM ( 'Inventory Values'[Value] )
)

Next, sort the table by the “Total Value” column in descending order:

Sorted Table =
RANKX ( 'Table', [Total Value], DESC )

Now, add a new column to the table that calculates the cumulative value for each item:

Cumulative Value =
VAR CurrentMonth = 'Sorted Table'[Month]
VAR CurrentMaterialID = 'Sorted Table'[Material ID]
RETURN
    CALCULATE (
        SUM ( 'Sorted Table'[Total Value] ),
        FILTER (
            ALL ( 'Sorted Table' ),
            'Sorted Table'[Material ID] = CurrentMaterialID
                && 'Sorted Table'[Month] <= CurrentMonth
        )
    )

Finally, create a line graph with the “Month” column on the x-axis and the “Cumulative Value” column on the y-axis.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.