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"....e...
Tips and Tricks
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.