Forum Discussion
Cumulative sum based on multiple slicers
Hello!
I am trying to figure out how to calculate a cummulative total of a column based on multiple slicers.
My data looks like the following:
| Fund | Type | Date | Value |
| 1 | A | 12/21/2021 | 100 |
| 1 | B | 3/15/2022 | 150 |
| 1 | A | 4/16/2022 | 50 |
| 2 | B | 11/14/2021 | 200 |
| 2 | B | 1/16/2022 | 150 |
| 2 | A | 2/16/2022 | 200 |
| 3 | B | 1/15/2021 | 50 |
| 3 | A | 10/8/2021 | 300 |
| 3 | B | 3/18/2022 | 150 |
So, if I were to set the slicers to Fund: 1 & 2, Type: A and Date<=2/18/22, I need a function to show me the total cummulative value of those selections. I bolded the values that would be summed together. So the cumm. value would be the following:
| Fund | Type | Date | Value | Cumm Value |
| 1 | A | 12/21/2021 | 100 | 300 |
| 1 | B | 3/15/2022 | 150 | 300 |
| 1 | A | 4/16/2022 | 50 | 300 |
| 2 | B | 11/14/2021 | 200 | 300 |
| 2 | B | 1/16/2022 | 150 | 300 |
| 2 | A | 2/16/2022 | 200 | 300 |
| 3 | B | 1/15/2021 | 50 | 300 |
| 3 | A | 10/8/2021 | 300 | 300 |
| 3 | B | 3/18/2022 | 150 | 300 |
I have tried the following for Cumm. Value but it doesn't work when I have when I slice on both Fund and Type
Cumm Value =
CALCULATE(
SUM('Data'[Value]),
FILTER(
'Data',
'Data'[Fund]=EARLIER('Data'[Fund])
&& 'Data'[Type]=EARLIER('Data'[Type])
&& 'Data'[Date] <= EARLIER('Data'[Date])
)
)
Any suggestions will be greatly appreciated. Thanks!
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated table.
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]) )Fund_select = DISTINCT('Table'[Fund])Type_select = DISTINCT('Table'[Type])2. Create measure.
Measure = var _Fund=SELECTCOLUMNS('Fund_select',"1",[Fund]) var _selectType=SELECTEDVALUE('Type_select'[Type]) var _selectdate=SELECTEDVALUE('Date'[Date]) return CALCULATE( SUM('Table'[Value]), FILTER(ALL( 'Table'), 'Table'[Fund] in _Fund &&'Table'[Date]<=_selectdate&&'Table'[Type]=_selectType))3. Result:
Use [Date] of table Date, [Fund] of table Fund_select, and [Type] of table Type_select as slicers respectively.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
- Ashish_Mathur
Super User
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated table.
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]) )Fund_select = DISTINCT('Table'[Fund])Type_select = DISTINCT('Table'[Type])2. Create measure.
Measure = var _Fund=SELECTCOLUMNS('Fund_select',"1",[Fund]) var _selectType=SELECTEDVALUE('Type_select'[Type]) var _selectdate=SELECTEDVALUE('Date'[Date]) return CALCULATE( SUM('Table'[Value]), FILTER(ALL( 'Table'), 'Table'[Fund] in _Fund &&'Table'[Date]<=_selectdate&&'Table'[Type]=_selectType))3. Result:
Use [Date] of table Date, [Fund] of table Fund_select, and [Type] of table Type_select as slicers respectively.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
