Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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.
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.
User | Count |
---|---|
90 | |
85 | |
65 | |
62 | |
58 |
User | Count |
---|---|
147 | |
113 | |
95 | |
81 | |
71 |