Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
This is my formula for the % change line measure:
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?
Solved! Go to 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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |