Reply
Frequent Visitor
Posts: 12
Registered: ‎09-15-2017
Accepted Solution

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!

 

2018-01-29 11_37_33-Inbox - Kan.Huang@ATSMRO.COM - Outlook.png

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

2018-01-31 08_08_43-data.csv - Excel.png

 

 

@Ashish_Mathur@v-jiascu-msft@nvpraveenyakkal

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

Datafile Pbix


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

Re: Calculate Cumulative Variance DAX help

Hi @optimusprime,

 

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

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.

View solution in original post


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

Hi @optimusprime,

 

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

Hi @optimusprime,

 

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

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

 

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

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