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
rilimonv
Employee
Employee

Measure or column to not show values > than X %

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: 

YoY% = CALCULATE(DIVIDE(sum('Report'[Revenue this fiscal]),sum('Report'[revenue Prior Year])))-1
I'm not sure if I should create a column or a measre to solve my issue, or if I need to go to power query (which I'm not 100% familiar with). Any suggestion?
Thanks!

 

1 ACCEPTED SOLUTION
artemus
Employee
Employee

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

View solution in original post

2 REPLIES 2
artemus
Employee
Employee

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 😃

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.

Top Solution Authors
Top Kudoed Authors