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 Actuals V Budget even though they are in the same column?

Below is how the layout of the dashboard is. I have slicers for each column and ideally if i filter by for example "Period 1" i would like the variance to show just period 1 also, aswell as fleet? so it works with the slicers?

 

 

 

Many thanks!

 

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.

10 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    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
    • jedlewis12345's avatar
      jedlewis12345
      Regular Visitor
      3 years ago

      Question.... If i was to add more Actuals for 2023, and more budgets for 2024, 2025 etc... would they filter through the slicers on the card as well??? I'm not sure if i need to extend the DAX to include columns [Year] and have 2022, 2023 etc.

      Would i be better off Concatenating my data in excel to "2022 Actuals" "2023 Actuals" "2023 Budget" "2024 Budget" Etc.. and then have multiple cards for variances?

      Ideally i want one card but i don't think its possible if i don't alter the data as the slicers can only do so much and it obviously won't slice the Dax to what i want compared.

       

      I think a card for each variance is needed?

      • DataInsights's avatar
        DataInsights
        Super User
        3 years ago

        jedlewis12345,

         

        When Actuals and Budget have the same grain, I prefer to create separate columns for Actuals and Budget. This reduces table size, simplifies DAX, and improves performance.

         

        To compare Actuals and Budget for different years, you'll need two Year slicers (one for Actuals, and one for Budget). These Year slicers will need to use disconnected tables (no relationship to the fact table) because filtering on a particular year in the date table would exclude rows for the other year. You can use DAX to filter for the Year in each slicer.