Need help creating a Delta column based on varying columns

[Anonymous]

I am trying to create a matrix/data table.

The Rows -  the rows are metric names for my KPI funnel. The names are defined within a custom MetricName datatable. ("Impressions", "Clicks", "CTR"....etc.)

The columns - Fiscal Years that the user can select using a multiselect filter (the user should select only two of these)

The values - uses the SELECTEDVALUE and SWITCH functionalities to display the values for each KPI in the MetricName datatable, like so:

Values =

VAR CurrentMetric = SELECTEDVALUE(Metrics[MetricName])

RETURN

SWITCH(

    CurrentMetric,

    "Impressions", CALCULATE(SUM('FLOWERS_KPI_F'[IMPRESSIONS])),

    "Clicks", CALCULATE(SUM('FLOWERS_KPI_F'[CLICKS])),

    "CTR", DIVIDE(

        CALCULATE(SUM('FLOWERS_KPI_F'[CLICKS])),

        CALCULATE(SUM('FLOWERS_KPI_F'[IMPRESSIONS]))

    ),.......

 Below is the current table. I need to add a third delta column that calculates the delta between the two columns. The columns selected don't have to be adjacent. For example, the user should be able view the delta between 2019 to 2022. I also will be doing this at the fiscal week or fiscal day level - so I would prefer if theres a flexible way to implement this delta.

[Anonymous]

I had something similar, but the column filter context made me return all zeroes for delta, so I used ALLSELECTED() to ignore the column fitler context:

YoY% Change = 
VAR AllYears = CALCULATETABLE(VALUES('AT_SEM_FISCAL_CALENDAR'[FY_N]), ALLSELECTED())
VAR MinYear = MINX(AllYears, 'AT_SEM_FISCAL_CALENDAR'[FY_N])
VAR MaxYear = MAXX(AllYears, 'AT_SEM_FISCAL_CALENDAR'[FY_N])


VAR Values_Year1 = 
CALCULATE(
    [Values],
    'AT_SEM_FISCAL_CALENDAR'[FY_N] = MinYear
)

VAR Values_Year2 = 
CALCULATE(
    [Values],
    'AT_SEM_FISCAL_CALENDAR'[FY_N] = MaxYear
)

RETURN 
IF(
    Values_Year1 <> 0,
    (Values_Year2 - Values_Year1) / Values_Year1,
    BLANK())

 The only issue now is im getting duplicate delta columns, presumable because there's a %YoY change/Delta for each fiscal year column. How do i remove the first one?

@Kedar_Pande 

[Anonymous]

Hi @Anonymous ,

According to your statement, I think you can try to DIY "Column Subtotal" in visual format.

Firstly, change the Subtotal label as "YoY% Change".

Then create a new measure based on [Values] measure.

Measure = 
VAR AllYears = CALCULATETABLE(VALUES('AT_SEM_FISCAL_CALENDAR'[FY_N]), ALLSELECTED())
VAR MinYear = MINX(AllYears, 'AT_SEM_FISCAL_CALENDAR'[FY_N])
VAR MaxYear = MAXX(AllYears, 'AT_SEM_FISCAL_CALENDAR'[FY_N])


VAR Values_Year1 = 
CALCULATE(
    [Values],
    'AT_SEM_FISCAL_CALENDAR'[FY_N] = MinYear
)

VAR Values_Year2 = 
CALCULATE(
    [Values],
    'AT_SEM_FISCAL_CALENDAR'[FY_N] = MaxYear
)
RETURN
IF(HASONEVALUE(AT_SEM_FISCAL_CALENDAR[FY_N]),[Values],(Values_Year2 - Values_Year1) / Values_Year1)

Use this measure in matrix value field. Then we need to create a measure for dynamic data format.

IF(HASONEVALUE(AT_SEM_FISCAL_CALENDAR[FY_N]),"0.00","0.00%")

Result is as below.

You can download my sample file to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

@Anonymous 

Create a Delta Measure:

Delta =
VAR SelectedYears = VALUES(YourYearTable[Year]) // Replace with your actual year table
VAR Year1 = SELECTEDVALUE(SelectedYears, 0, 1) // Get the first selected year
VAR Year2 = SELECTEDVALUE(SelectedYears, 1, 0) // Get the second selected year

RETURN
IF(
Year1 <> 0 && Year2 <> 0,
[Values for Year2] - [Values for Year1], // Replace with your measure for each selected year
BLANK()
)

Adjust your existing values measure

Values_Year1 =
CALCULATE(
[Values],
FILTER(
'YourYearTable',
'YourYearTable'[Year] = SELECTEDVALUE(YourYearTable[Year], 0)
)
)

Values_Year2 = 
CALCULATE(
[Values], 
FILTER(
'YourYearTable', 
'YourYearTable'[Year] = SELECTEDVALUE(YourYearTable[Year], 1)
)
)

💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn