Running Sum # and Running Sum % based on status Power BI (DAX)

Question

I am struggling with creating Running Summ for value based on two statuses I have in my table. The problem is that I do not have dates, only text and numeric values.

I even created Index table but this does not help. Please have a look at my data:

I need to calculate Running Sum for Column Distribution in another Column but for status "Gains" and "Gross" separately. So the Running Sum is calculated for "Gains" and then starts again for "Gross".

Then I need to use that to create Percent of Total - also separately for "Gains" only and for "Gross" only. I reviewed many forums, tutorials and could not find anything working for my data.

Can you please help me out?

Data sample:

Score Range	tier	Distribution	Status	General Index
1-100	Tier III	38	Gains	1
100-125	Tier III	33	Gains	2
125-150	Tier III	49	Gains	3
150-175	Tier III	46	Gains	4
175-200	Tier III	76	Gains	5
200-225	Tier II	135	Gains	6
225-250	Tier I	348	Gains	7
250-275	Tier I	417	Gains	8
275-300	Tier I	541	Gains	9
300-325	Tier I	682	Gains	10
325-350	Tier I	910	Gains	11
350-375	Tier I	781	Gains	12
375-400	Tier I	754	Gains	13
400-425	Tier I	551	Gains	14
425-450	Tier I	396	Gains	15
450-475	Tier I	214	Gains	16
475-500	Tier I	50	Gains	17
500 +	Tier I	2	Gains	18
No Score	Tier I	176	Gains	19
1-100	Tier III	350	Gross	1
100-125	Tier III	270	Gross	2
125-150	Tier III	404	Gross	3
150-175	Tier III	463	Gross	4
175-200	Tier III	465	Gross	5
200-225	Tier II	512	Gross	6
225-250	Tier I	599	Gross	7
250-275	Tier I	700	Gross	8
275-300	Tier I	897	Gross	9
300-325	Tier I	1089	Gross	10
325-350	Tier I	1415	Gross	11
350-375	Tier I	1183	Gross	12
375-400	Tier I	1104	Gross	13
400-425	Tier I	725	Gross	14
425-450	Tier I	535	Gross	15
450-475	Tier I	282	Gross	16
475-500	Tier I	67	Gross	17
500 +	Tier I	2	Gross	18
No Score	Tier I	624	Gross	19

I am trying to make calculations as on below screen:

Thanks,

when you write that you need to sum the distribution in another column but for the status Gains and Gross separately, you mean that you need to add them to two separate columns, not in one, correct? — sergiom
The index in your data is not unique, it seems to depend on the status, can you replace it with a unique index instead? — sergiom
Hi, sure. I added an excel file with columns calculation I need to have in the data set. This is very dificult to get via DAX, can you help me with that? — Pawel_L

sergiom sergiom · Accepted Answer · 2021-01-06T20:13:22

I shortened the names of the columns a bit to make the result table stay in the answer I named the sample data table "Status"

For the Running Sum we iterate filtering the Status of the current row and an index less than or equal to the current row's

Running Sum = 
VAR CurrentRowStatus = Scores[Status]
VAR CurrentIndex = Scores[General Index]
VAR Result =
    SUMX(
        FILTER(
            Scores,
            Scores[Status] = CurrentRowStatus
                && Scores[General Index] <= CurrentIndex
        ),
        Scores[Distribution]
    )
RETURN
    Result

For the percentages calculated columns we need to compute the total, therefore we use MAXX over the Status table filtered using the current row status

percent = 
VAR CurrentRowStatus = Scores[Status]
VAR Total =
    MAXX(
        FILTER( Scores, Scores[Status] = CurrentRowStatus ),
        Scores[Running Sum]
    )
VAR Result =
    DIVIDE( Scores[Distribution], Total )
RETURN
    Result

the cumulative percent calculated column is similar, it just uses the Running Sum calculated column instead of the Distribution

cumulative percent = 
VAR CurrentRowStatus = Scores[Status]
VAR Total =
    MAXX(
        FILTER( Scores, Scores[Status] = CurrentRowStatus ),
        Scores[Running Sum]
    )
VAR Result =
    DIVIDE( Scores[Running Sum], Total )
RETURN
    Result

This is the resulting table

Score Range	tier	Distribution	Status	General Index	Running Sum	percent	cumulative percent
1-100	Tier III	38	Gains	1	38	0.6%	0.6%
100-125	Tier III	33	Gains	2	71	0.5%	1.1%
125-150	Tier III	49	Gains	3	120	0.8%	1.9%
150-175	Tier III	46	Gains	4	166	0.7%	2.7%
175-200	Tier III	76	Gains	5	242	1.2%	3.9%
200-225	Tier II	135	Gains	6	377	2.2%	6.1%
225-250	Tier I	348	Gains	7	725	5.6%	11.7%
250-275	Tier I	417	Gains	8	1142	6.7%	18.4%
275-300	Tier I	541	Gains	9	1683	8.7%	27.1%
300-325	Tier I	682	Gains	10	2365	11.0%	38.2%
325-350	Tier I	910	Gains	11	3275	14.7%	52.8%
350-375	Tier I	781	Gains	12	4056	12.6%	65.4%
375-400	Tier I	754	Gains	13	4810	12.2%	77.6%
400-425	Tier I	551	Gains	14	5361	8.9%	86.5%
425-450	Tier I	396	Gains	15	5757	6.4%	92.9%
450-475	Tier I	214	Gains	16	5971	3.5%	96.3%
475-500	Tier I	50	Gains	17	6021	0.8%	97.1%
500 +	Tier I	2	Gains	18	6023	0.0%	97.2%
No Score	Tier I	176	Gains	19	6199	2.8%	100.0%
1-100	Tier III	350	Gross	1	350	3.0%	3.0%
100-125	Tier III	270	Gross	2	620	2.3%	5.3%
125-150	Tier III	404	Gross	3	1024	3.5%	8.8%
150-175	Tier III	463	Gross	4	1487	4.0%	12.7%
175-200	Tier III	465	Gross	5	1952	4.0%	16.7%
200-225	Tier II	512	Gross	6	2464	4.4%	21.1%
225-250	Tier I	599	Gross	7	3063	5.1%	26.2%
250-275	Tier I	700	Gross	8	3763	6.0%	32.2%
275-300	Tier I	897	Gross	9	4660	7.7%	39.9%
300-325	Tier I	1089	Gross	10	5749	9.3%	49.2%
325-350	Tier I	1415	Gross	11	7164	12.1%	61.3%
350-375	Tier I	1183	Gross	12	8347	10.1%	71.4%
375-400	Tier I	1104	Gross	13	9451	9.4%	80.9%
400-425	Tier I	725	Gross	14	10176	6.2%	87.1%
425-450	Tier I	535	Gross	15	10711	4.6%	91.7%
450-475	Tier I	282	Gross	16	10993	2.4%	94.1%
475-500	Tier I	67	Gross	17	11060	0.6%	94.6%
500 +	Tier I	2	Gross	18	11062	0.0%	94.7%
No Score	Tier I	624	Gross	19	11686	5.3%	100.0%

Running Sum # and Running Sum % based on status Power BI (DAX)

1 Answers