Forum Discussion

oneillp111's avatar
oneillp111
Frequent Visitor
5 years ago

Cumulative matrix measure

I am trying to create a measure that will do a cumulative total within the matrix.  Below is a snipit of the data, the 

The measure would give me the sum of the value and the value before it so for the first column it would be

4

6

10

instead of

4

2

4

I tried to do it with the following measure that I found in this forum but it sums everything in the table without taking into account filters on the page. The Sum MQ field is just a 1 so i could use sum instead of count.

 

Running MQ Sum = CALCULATE(sum('Data'[sum MQ]),filter('Data','Data'[Created on]<=EARLIER('Data'[Created on])))

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    oneillp111 , try like


    Running MQ Sum = CALCULATE(sum('Data'[sum MQ]),filter('Data','Data'[fail MQ]<=max('Data'[fail MQ])))

    or

    Running MQ Sum = CALCULATE(sum('Data'[sum MQ]),filter(allselected('Data'),'Data'[fail MQ]<=max('Data'[fail MQ]) && 'Data'[Helper2] =max('Data'[Helper2])))

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi, oneillp111 

    You need to create an index column  in "Transform Data" after sorting by date and MQ in ascending order.

    Then please try measure as below:

    Measure =
CALCULATE (
    SUM ( Data[sum MQ] ),
    FILTER (
        ALL ( Data ),
        Data[Index] <= MAX ( Data[Index] )
            && Data[Created on] = MAX ( Data[Created on] )
    )
)

    Then result will show as below:

     

    Please check attached pbix for more detail.

    If it doesn't meet your requirement,please share more details.

     

    Best Regards,
    Community Support Team _ Eason