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 do it easily in Excel), shown in figure below. See formulas. Ashish_Mathurv-jiascu-msftAnonymous

 

 

Ashish_Mathurv-jiascu-msftAnonymous

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

Datafile Pbix

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

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

  • 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

  • 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

  • bushin's avatar
    bushin
    New Member
    7 years ago

    Hi,

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

     

    Tks