Forum Discussion

YPBI's avatar
YPBI
Frequent Visitor
2 years ago

Running Total % on Measure

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?

 

 

 

Need Help

12 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

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

    • YPBI's avatar
      YPBI
      Frequent Visitor
      2 years ago

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

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    YPBI you can try something like this:

     

    RT Measure = 
CALCUALTE ( 
    [Existing Measure], 
    FILTER ( 
          ALL ( 'Grouping Table' ), 
          'Grouping Table'[Id] <= MAX ( 'Grouping Table'[Id] ) 
    )
)
    • YPBI's avatar
      YPBI
      Frequent Visitor
      2 years ago

      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

       

       

       

    • YPBI's avatar
      YPBI
      Frequent Visitor
      2 years ago

      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()))

       

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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