topic How to get cumulative amount and cumulative % for Overall and based on Slicer in DAX Commands and Tips

How to get cumulative amount and cumulative % for Overall and based on Slicer

NickzNickz — Wed, 21 Feb 2024 03:42:22 GMT

Hi ...
I have created table profit and data as below:

Date	Collection ($)	Costing ($)	Profit ($)
30/1/2023	4,000	9,421	(5,421)
24/2/2023	22,500	17,077	5,423
10/3/2023	25,500	18,157	7,343
11/3/2023	27,000	21,183	5,817
7/4/2023	1,950	7,301	(5,351)
1/6/2023	3,000	900	2,100
31/7/2023	12,750	6,978	5,772
31/7/2023	6,000	5,197	803
12/8/2023	32,000	21,678	10,322
5/9/2023	11,000	4,782	6,218
5/9/2023	4,550	3,980	570
23/10/2023	3,000	4,556	(1,556)
23/10/2023	31,500	7,411	24,089
24/10/2023	900	0	900
3/1/2024	5,000	3,339	1,661
9/1/2024	5,500	3,288	2,212
20/1/2024	4,000	2,027	1,973
29/1/2024	4,000	2,027	1,973
4/2/2024	17,000	12,444	4,556
10/2/2024	8,250	7,728	522
16/2/2024	15,000	14,061	939
20/2/2024	25,500	10,241	15,259

Based on above data, I need to create a measure for Cumulative Profit ($) and Cumulative Profit (%).
My dashboard setup, I have Year Slicer (dropdown / Multi-select with CTRL), Table, Card and Line Chart.
How can I get the value based on my slicer selection (All or Year Selected).
Regards,

NickzNickz

Re: How to get cumulative amount and cumulative % for Overall and based on Slicer

Jihwan_Kim — Wed, 21 Feb 2024 04:37:25 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Profit: = SUMX(Data, Data[Collection ($)]-Data[Costing ($)])

WINDOW function (DAX) - DAX | Microsoft Learn

Cumulative profit %: = VAR _allselectedprofit = CALCULATE ( [Profit:], WINDOW ( 1, ABS, -1, ABS, ALLSELECTED ( 'Calendar'[Date], 'Calendar'[Year]), ORDERBY ( 'Calendar'[Date], ASC ) ) ) VAR _cumulativeprofit = CALCULATE ( [Profit:], WINDOW ( 1, ABS, 0, REL, ALLSELECTED ( 'Calendar'[Date], 'Calendar'[Year] ), ORDERBY ( 'Calendar'[Date], ASC ) ) ) RETURN IF ( NOT ISBLANK ( [Profit:] ), DIVIDE ( _cumulativeprofit, _allselectedprofit ) )

Re: How to get cumulative amount and cumulative % for Overall and based on Slicer

NickzNickz — Wed, 21 Feb 2024 05:01:23 GMT

Hi Jihwan_Kim ,

I am not sure how your semantic model looks like > My date data in the same table.
How can I get value of Cumulative Profit ($).

Btw do i need to create additional column for Year and Month. My date is in Date level and my data in dashboard is by Month and my slicer is in Year.

Example:

Regards,

NickzNickz

Re: How to get cumulative amount and cumulative % for Overall and based on Slicer

Anonymous — Thu, 22 Feb 2024 08:22:27 GMT

Hi NickzNickz ,

You can create the measure as below to get it, please find the details in the attachment.

Cumulative Profit ($) = CALCULATE ( SUM ( 'Table'[Profit ($)] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] ) ) )

Cumulative Profit (%) = VAR _all = CALCULATE ( SUM ( 'Table'[Profit ($)] ), ALLSELECTED ( 'Table' ) ) RETURN DIVIDE ( [Cumulative Profit ($)], _all )

Best Regards