Same month last year Percent change in Line and Clustered Column Chart

[Anonymous]

Hello,

This is going to be a bit hard to explain so bare with me.

I have a measure that uses SWITCH to let users select which field ( for example net revenue) to view in a Line and Clustered Column Chart using this method (https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Measures-Titles-Using-SWITCH/td-p/20...).

The chart uses a date hierarchy as the shared axis and year as the Column Series and my SWITCH measure in the column values and another measure which calculates the YoY % change of the selected SWITCH measure as the line value.

Here is the format of the switch measure:

SelectedMetric =

IF (

HASONEVALUE ('Metric Select'[P&L SELECT] ),

SWITCH (

VALUES ('Metric Select'[P&L SELECT] ),

"Net Revenue", SUM ( 'P&L'[P&L.Net Revenue] ),

"Total COGS", SUM ( 'P&L'[P&L.Total COGS] ),

"Services COGS", SUM ( 'P&L'[P&L.Services COGS] ),

"Staff Payroll and Benefits", SUM( 'P&L'[P&L.Staff Payroll and Benefits]),

"Product COGS", SUM('P&L'[P&L.Product COGS]),

"EBITDA",SUM('P&L'[P&L.Hospital EBITDA]),

"ACT",ACT[ACT pt1]

),

SUM ( 'P&L'[P&L.Net Revenue] )

)

I have 2 types of fields in the SWITCH measure that users can select from. SUMs of column values straight from the fact table, or a measure which is a ratio of two numbers.

This is the formula for the measure:

ACT pt1 = (SUM('P&L'[P&L.Net Revenue])/SUM(ACT[Transactions per Month]))

This is my formula for the % change line measure:

SelectedMetric YoY% Line =

VAR __PREV_YEAR = CALCULATE([SelectedMetric], DATEADD('dimdate2'[Date].[Date], -1, YEAR))

VAR per = DIVIDE([SelectedMetric]-__PREV_YEAR,__PREV_YEAR)

    RETURN

        if(or(per = -1, per = 0),BLANK(),per)

the if statement is to deal with months with blank values.

The issue is that the displayed value in the chart for the SUM of fact table columns (ex: SUM ( 'P&L'[P&L.Net Revenue] ))  is always 100% + % change and if I try to fix this by subtracting a 1.0, that screws up the displayed % change for the ACT[ACT pt1] measure.

Output when I subtract -1 from the YoY measure ( this is the way I want it displayed)

Output with the default % change formula (incorrect output)

When I correct the output by -1 in the measure, it screws up the output for the ACT measure, which for some reason does not require subtracting 1 from the output of the % change formula.

How do I get the outputs of the % change formula to be consistant?

[Anonymous · Accepted solution]

Hey I found a solution, probably not the most elegant, but it worked for me.

I put a SWITCH function in the YoY% formula and created YoY% calculations for each metric, subtracting 1 from the calculations for the fields that were not displaying properly.