Calculate a Monthly and YTD variance from Rows and Measures

[Anonymous]

I have searched the forums but still cannot figure out how to do this. Below is two different examples of reports of my data. This data will need to be presented at the most granular level (Contract ID) and rolled up into various groups by region and then overall. In this example, I am showing 2020-01.

 

Here is how the data is formatted in the table.

I need to be able to calculate the variance in the Act vs. Bud for all data elemenets [Comp-Prov], [Cases], [Prov Comp/Case] at each level regardless of how I roll-up the data. Ideally I would like to calculate the variance % so that I can use conditional formatting to make it red, green or yellow.

I also want it to work on a YTD basis so if I filter the data to 2020-01, 2020-02 and 2020-03, I want it to show me the YTD Act vs. Bud variances.

Can anyone assist me with this? I would be most grateful. 

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Variance_YTD =
VAR t =
    FILTER (
        CALCULATETABLE (
            ALLSELECTED ( 'Table' ),
            ALL ( 'Table'[YYYY-MM] )
        ),
        LEFT (
            'Table'[YYYY-MM],
            4
        )
            = LEFT (
                MAX ( 'Table'[YYYY-MM] ),
                4
            )
    )
VAR t2 =
    SUMMARIZE (
        t,
        [Contract ID],
        "Difference", CALCULATE (
            SUM ( [Comp-Prov] )
                / SUM ( [Cases] ),
            FILTER (
                'Table',
                'Table'[Scenario] = "Act"
            )
        )
            - CALCULATE (
                SUM ( [Comp-Prov] )
                    / SUM ( [Cases] ),
                FILTER (
                    'Table',
                    'Table'[Scenario] = "Bud"
                )
            )
    )
VAR averaget2 =
    AVERAGEX (
        t2,
        [Difference]
    )
RETURN
    SQRT (
        SUMX (
            t2,
            ( [Difference] - averaget2 ) * ( [Difference] - averaget2 )
        )
            / (
                COUNTROWS ( t2 ) - 1
            )
    )

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared？

Best regards,

[Anonymous]

Thank you for offering a solution. I will try this later this week and let you know if it works.

[Anonymous]

Hello @amitchandak  and @v-lid-msft 

Thank you both for your helpful replies to my post.

I ended up deciding that my database design was the problem. So, I ended up re-designing all of my data elements with ACT and BUD in different columns. Then I just used the VAR calculation to calculate the difference.

Everything is working now so thank you!

[amitchandak · Accepted solution]

I think you should have done pivoted the data to bring budget and actual as columns .

https://radacad.com/pivot-and-unpivot-with-power-bi

Budget Avg = divide( sumx([Comp-Prov],SCENARIO = "Bug" ),sumx([Cases],SCENARIO = "Bug" ) )
Actual Avg = divide( sumx([Comp-Prov],SCENARIO = "Act" ),sumx([Cases],SCENARIO = "Act" ) )

variance =[Budget Avg ] -[Actual Avg] // you can change the position

Now for YTD and LYTD , i prefer datesytd or totalytd.
Convert you month to date
date = date(left([YYYY-MM],4),right([YYYY-MM],2),1)
Create a date table and join it with that

YTD Sales = CALCULATE([variance],DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(variance],DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/