Calculate Cumulative sum of the measure on Field Parameter

[vin26]

Please help me to calculate Cumulative sum of the measure.

I have created a model by using Field Parameter to switch the Dimensions. I wanted to dynamically calculate cumulative sum on the measure and on selected dimension. Please find below data and screenshots.

Table 1

ID	Region	Company	Brand
1	Region1	Company1	Brand1
2	Region1	Company2	Brand2
3	Region1	Company3	Brand3
4	Region1	Company4	Brand4
5	Region1	Company5	Brand5
6	Region1	Company1	Brand6
7	Region1	Company2	Brand7
8	Region1	Company3	Brand8
9	Region1	Company4	Brand9
10	Region2	Company5	Brand10
11	Region2	Company1	Brand11
12	Region2	Company2	Brand12
13	Region2	Company3	Brand13
14	Region2	Company4	Brand14
15	Region2	Company5	Brand15
16	Region2	Company1	Brand16
17	Region2	Company2	Brand17
18	Region2	Company3	Brand18
19	Region3	Company4	Brand19
20	Region3	Company5	Brand20
21	Region3	Company1	Brand21
22	Region3	Company2	Brand22
23	Region3	Company3	Brand23
24	Region3	Company4	Brand24
25	Region3	Company5	Brand25
26	Region3	Company1	Brand26

Table 2

ID	Sales	Unit
1	1651	427
2	1770	215
3	1794	351
4	1964	407
5	1302	298
6	1396	382
7	1861	423
8	1830	433
9	1201	269
10	1749	401
11	1464	278
12	1726	313
13	1903	215
14	1507	222
15	1427	218
16	1599	391
17	1656	335
18	1297	214
19	1983	439
20	1159	297
21	1201	289
22	1624	303
23	1631	374
24	1734	438
25	1444	487
26	1800	466

Data Model

[Anonymous · Accepted solution]

Hi, @vin26 

First, create a parameter and put three fields into it

Then create a new measure and try the following DAX:

Cumulative Sales Dynamic = 
VAR SelectedDimension = 
    SWITCH(
        TRUE(),
        ISINSCOPE('Table'[Region]), "Region",
        ISINSCOPE('Table'[Company]), "Company",
        ISINSCOPE('Table'[Brand]), "Brand",
        "Other"
    )
RETURN
    SWITCH(
        SelectedDimension,
        "Region",
        CALCULATE(
            SUM('Table (2)'[Sales]),
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Region] <= MAX('Table'[Region])
            )
        ),
        "Company",
        CALCULATE(
            SUM('Table (2)'[Sales]),
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Company] <= MAX('Table'[Company])
            )
        ),
        "Brand",
        CALCULATE(
            SUM('Table (2)'[Sales]),
            FILTER(
                ALLSELECTED('Table'),
                VALUE(MID('Table'[Brand],6,3)) <= VALUE(MID(MAX('Table'[Brand]),6,3))
            )
        ),
        BLANK()
    )

Put the parameter field and the measure into table view, the following is previe. When you select other fields in the slicer, the measure dynamically adds them up:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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