Calculate Cumulative Variance DAX help

[Anonymous]

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@Anonymous

@Ashish_Mathur@v-jiascu-msft@Anonymous

Here is the file contains my data, can you please help us?

Datafile Pbix

[bushin]

Hi,

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

Tks

[s2anya]

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

add following columns:

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

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

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

[Ashish_Mathur]

Hi,

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

[v-jiascu-msft]

Hi @Anonymous,

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

[Anonymous]

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