## Desktop

Frequent Visitor
Posts: 12
Registered: ‎09-15-2017

# Calculate Cumulative Variance DAX help

[ Edited ]

Is there a way to do a cumulative Variance in DAX, add a new column? something like this? Thank you!

Thank you guys. I guess I wasn't clear. Here is what I'd like to do (and I was able to do it easily in Excel), shown in figure below. See formulas. @Ashish_Mathur@v-jiascu-msft@nvpraveenyakkal

Datafile Pbix

Accepted Solutions
Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

## Re: Calculate Cumulative Variance DAX help

The logic is different in the Power BI. Please try a measure like this:

```Measure 4 =
VAR maxindex =
CALCULATE (
MAX ( Data[Index] ),
FILTER ( ALL ( Sort ), Sort[Sort] = MAX ( Sort[Sort] ) - 1 )
)
RETURN
IF (
ISBLANK ( maxindex ),
BLANK (),
[CumulRate]
- CALCULATE (
SUM ( Data[DOLLARS] ),
FILTER ( ALL ( Data ), Data[Index] <= maxindex )
)
/ CALCULATE (
SUM ( Data[HRS] ),
FILTER ( ALL ( Data ), Data[Index] <= maxindex )
)
)```

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

All Replies
Highlighted
Member
Posts: 94
Registered: ‎05-22-2017

## Re: Calculate Cumulative Variance DAX help

On What basis did you sort the rows in that order ?

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

## Re: Calculate Cumulative Variance DAX help

Can you share the original data please? It's an expected result here. We need the original data to write a DAX formula.

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Posts: 3,934
Registered: ‎01-14-2017

## Re: Calculate Cumulative Variance DAX help

Hi,

Accumulation has to happen on some basis - be it Date or a serial number?  What is your basis of accumulation?

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

## Re: Calculate Cumulative Variance DAX help

The logic is different in the Power BI. Please try a measure like this:

```Measure 4 =
VAR maxindex =
CALCULATE (
MAX ( Data[Index] ),
FILTER ( ALL ( Sort ), Sort[Sort] = MAX ( Sort[Sort] ) - 1 )
)
RETURN
IF (
ISBLANK ( maxindex ),
BLANK (),
[CumulRate]
- CALCULATE (
SUM ( Data[DOLLARS] ),
FILTER ( ALL ( Data ), Data[Index] <= maxindex )
)
/ CALCULATE (
SUM ( Data[HRS] ),
FILTER ( ALL ( Data ), Data[Index] <= maxindex )
)
)```

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 7
Registered: ‎12-06-2017

## Re: Calculate Cumulative Variance DAX help

Hi,

If are comfortable in making a new table then you can follow the following steps to achieve the required difference:

Make a new table:

Table = GROUPBY(Data,Data[Category],"cuml",SUMX(CURRENTGROUP(),Data[Dollars]),"hour",SUMX(CURRENTGROUP(),Data[Hrs]))

1. Index = RANKX('Table','Table'[Data_Category])

2. cumldollar = CALCULATE(SUM('Table'[cuml]),ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index]))

3. cumlhour = CALCULATE(SUM('Table'[hour]),ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index]))

4. CumulRate = 'Table'[cumldollar]/'Table'[cumlhour]

5. Column = 'Table'[CumulRate]-IF('Table'[Index]=1,'Table'[CumulRate],LOOKUPVALUE('Table'[CumulRate],'Table'[Index],'Table'[Index]-1))

Regards,

Sanya Chauhan

Occasional Visitor
Posts: 1
Registered: ‎01-26-2019

## Re: Calculate Cumulative Variance DAX help

Hi,

the file is not available. Can share one more time?

Tks