Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate a Monthly and YTD variance from Rows and Measures

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. 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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/

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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!