Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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)

correctoutput.png

 

Output with the default % change formula (incorrect output)

 

wrongoutput.png

 

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?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

 

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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.

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.