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.

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?