Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate Cumulative Variance DAX help

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 d...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

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

    Calculate_Cumulative_Variance_DAX_help

     

    Best Regards,

    Dale

s2anya's avatar
s2anya
Frequent Visitor
8 years ago

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