Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I am trying to have data visuals that show year over year percentage growth, however I want those %'s greater than x% or less than y% to be marked as "N/A". I have tried several options but can't make it work. this might be because my YoY% is a measure I created from 2 columns of my data set (i have revenue from this year vs. revenue from last year, so then created a YoY% measure using this dax formula:
Solved! Go to Solution.
You should first ask yourself, why would a value be greater than x% or less than y%.
As to if you can use Power Query: Doesn't look like it. Power query allows you to calculate new column values, but does not allow you to do measures. If you allow any filtering on the Report table, or have it categorized in some way, then you will not be able to use Power query.
As for the answer, you can do something like:
YoY% = VAR p = CALCULATE(DIVIDE(sum('Report'[Revenue this fiscal]),sum('Report'[revenue Prior Year])))-1
RETURN IF(p > 1 || p < 0 || p = BLANK(), "N/A", p)
Where p > 1 is your more than y% (1 is 100%) and p < 0 is your less than x%. Note use Shift-Enter to add a new line
You should first ask yourself, why would a value be greater than x% or less than y%.
As to if you can use Power Query: Doesn't look like it. Power query allows you to calculate new column values, but does not allow you to do measures. If you allow any filtering on the Report table, or have it categorized in some way, then you will not be able to use Power query.
As for the answer, you can do something like:
YoY% = VAR p = CALCULATE(DIVIDE(sum('Report'[Revenue this fiscal]),sum('Report'[revenue Prior Year])))-1
RETURN IF(p > 1 || p < 0 || p = BLANK(), "N/A", p)
Where p > 1 is your more than y% (1 is 100%) and p < 0 is your less than x%. Note use Shift-Enter to add a new line
Thanks, @artemus! this worked perfectly. I was beginning to figure it out with a nested if formula but I think your suggestion is cleaner and easier to modify if i need to. Appreciate it 😃
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.