Get Variance from the same column

[jedlewis12345]

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!

[DataInsights · Accepted solution]

@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 · Accepted solution]

@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]

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]

@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.

[jedlewis12345]

Makes sense!

My issue is my layout in excel. I have this layout below,

I have then unpivoted columns F-R, which then looks like this below. I don't really know how else to do it. unless i create different sheets in excel for each years actuals V Budget and upload them to PBI as different queries?

[DataInsights]

@jedlewis12345,

After you unpivot columns F-R, pivot column Actuals/Budget:

This will create separate columns for Actuals and Budget.

[jedlewis12345]

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 · Accepted solution]

@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]

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]

@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.

[jedlewis12345]

Hi @DataInsights 

You really are a super user! Thanks so much.... saved me alot of headaches!