Forum Discussion

jedlewis12345's avatar
jedlewis12345
Regular Visitor
3 years ago
Solved

Get Variance from the same column

Hi all, I have my data laid out in the following format: There are 2 years of data - 2022 Actuals and 2023 Budget. How would i get a Card Visual to display the variance for Actua...
Need Help
Show and Tell
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    3 years ago

    jedlewis12345,

     

    Try this measure:

     

    Variance =
VAR vActuals =
    CALCULATE ( SUM ( Table1[Value] ), Table1[Actuals/Budget] = "Actuals" )
VAR vBudget =
    CALCULATE ( SUM ( Table1[Value] ), Table1[Actuals/Budget] = "Budget" )
VAR vResult = vBudget - vActuals
RETURN
    vResult
  • DataInsights's avatar
    DataInsights
    3 years ago

    jedlewis12345,

     

    Since you are trying to slice by different years simultaneously, you'll have to create separate Year tables to use for slicers. Alternatively, you could create a column Reporting Year and populate it with 2022 for the "2022 Actuals" and "2023 Budget" rows. Then you could slice by Reporting Year.

jedlewis12345's avatar
jedlewis12345
Regular Visitor
3 years ago

This looks good,

But then they are 2 seperate entities which i can not slice in my matrix table.. it just makes 2 columns per "Period"

 

DataInsights's avatar
DataInsights
Super User
3 years ago

jedlewis12345,

 

Since you are trying to slice by different years simultaneously, you'll have to create separate Year tables to use for slicers. Alternatively, you could create a column Reporting Year and populate it with 2022 for the "2022 Actuals" and "2023 Budget" rows. Then you could slice by Reporting Year.

  • jedlewis12345's avatar
    jedlewis12345
    Regular Visitor
    3 years ago

    Yes that makes sense.

    I will make 2 columns. actuals/ budget then year and slice them both to what i want to see like below.

     

     

     

    Finally: If i wanted to add more measures in to this DAX below, to include "2022" from [Year] Column, "Actuals" from [Actuals/Budget] Column, to then less "2023" from [Year] Column, "Budget" from Budget column. How would i do it?

    Same answer as the one below but from 4 columns rather than 2?

     

    22 v 23 Variance =
    VAR vActuals =
        CALCULATE ( SUM ( '22 Actuals'[Value] ), '22 Actuals'[Actuals/Budget] = "2022 Actuals" )
    VAR vBudget =
        CALCULATE ( SUM ( '22 Actuals'[Value] ), '22 Actuals'[Actuals/Budget] = "2023 Budget" )
    VAR vResult = vBudget - vActuals
    RETURN
        vResult
     
    Thanks!
  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    jedlewis12345,

     

    What are the 4 columns? I noticed that your table name contains the year. It's better to have generic table names and use columns to distinguish the data. Otherwise, you might have another table '23 Actuals', '24 Actuals', etc. These can be appended into one master table, and use the Year (and Reporting Year) column to distinguish the data.