Running Total % on Measure

[YPBI]

Hi - I want to create running total column based on the values in the % Scaled Scores column. The probem is that the said column is a measure, so solutions that use SUM won't work. What would the DAX for the running total look like?

[Dangar332]

hi, @YPBI 

try below measure
adjust your table name

result =

var a = CALCULATE(SUM('Table'[scaled score]),KEEPFILTERS('Table'[sort ss grouping]<=MAX('Table'[sort ss grouping])),REMOVEFILTERS('Table'[scaled scores grouping]))

var b = CALCULATE(SUM('Table'[scaled score]),REMOVEFILTERS('Table'[scaled scores grouping]))

return

DIVIDE(a,b)

and YAH! here you use highest sort ss grouping value for low range of scaled scores grouping so you get desc answer 

you can download .pbix file of this solution click Here 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[YPBI]

Would love to share but the dataset contains all sorts of sensitive information...

These are the relevant columns at play though:

% Scaled Scores measure:

% Scaled Scores = 
DIVIDE(
    COUNT(TestData[Scaled Score]),
        CALCULATE(
            COUNT(TestData[Scaled Score]),ALLSELECTED()))

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[YPBI]

Thanks for the responses, however the end result needs to look like this:

The running total should cumulatively sum each percentage in the % Scaled Score column.

@Ashish_Mathur @Dangar332 @parry2k 

[Ashish_Mathur]

That is exactly what my solution does.

[YPBI]

Hi -

I think the problem is that the SS measure you've created is a sum. It needs to be a count, as the Count of Scaled Scores column is basically the number of students who fell within that grouping. I changed the SUM to COUNT but then the Running total SS measure doesn't work...

[parry2k]

@YPBI you can try something like this:

RT Measure = 
CALCUALTE ( 
    [Existing Measure], 
    FILTER ( 
          ALL ( 'Grouping Table' ), 
          'Grouping Table'[Id] <= MAX ( 'Grouping Table'[Id] ) 
    )
)

[YPBI]

That doesn't seem to get me the desired result.

RT Measure = 
CALCULATE(  
    [% Scaled Scores], 
    FILTER ( 
          ALL ( TestData[Sort SS Grouping]), 
          'TestData'[Sort SS Grouping] <= MAX ( TestData[Sort SS Grouping]) 

I tried swapping the Sort SS Grouping with Scaled Scores Grouping and got this...first two rows sum correctly

[littlemojopuppy]

@YPBI listen to @parry2k.  He's one of the best Super Users and has helped me numerous times!  🙂

[parry2k]

@YPBI it is possible if you have a number column to sort scaled scores grouping column

[YPBI]

I actually have one already and the Scaled Scores Grouping is sorted by that column...how does that fit in here?