Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Need help creating a Delta column based on varying columns

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.

 

 
 

 

Tips and Tricks

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.