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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
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