- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Condition in a measure - how to ignore blank nomin...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

katalay

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2017
07:38 AM

Hi,

I am working on the matrix below:

I used the following measure to create the YOY calculation:

YOY = sum(YOY[Yr_2017]) / sum(YOY[Yr_2016]) -1

Is there a way to add a condition to the formula above so that if let's say 2017 is blank there is no calculation, meaning I see blank cells instead of -100.0% ? (example below)

Thank you for the help!

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

erik_tarnvik

Established Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2017
07:59 AM

YOY = IF(ISBLANK(sum(YOY[Yr_2017])) || ISBLANK(sum(YOY[Yr_2016])),

BLANK(),

sum(YOY[Yr_2017]) / sum(YOY[Yr_2016]) -1)

erik_tarnvik

Established Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2017
05:55 AM

Hi @katalay, you are right, I failed to see that side effect. Turned out I struggled a little to solve this and had to take a different approach. I am still not satisfied that this is the simplest possible solution but it does work using my example data:

ALL_2016_NEW = CALCULATE ( SUM ( YOY[MKT_2016] ), SELECTCOLUMNS(FILTER ( SUMMARIZE(YOY,YOY[Month],YOY[MKT_2017]), YOY[MKT_2017] <> 0),"Month", YOY[Month]) )

12 REPLIES 12

erik_tarnvik

Established Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2017
07:59 AM

BLANK(),

sum(YOY[Yr_2017]) / sum(YOY[Yr_2016]) -1)

katalay

Frequent Visitor

Re: Condition in a measure - how to ignore blank nominator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2017
09:12 AM

Thank you Erik that helped a lot.

Is it also possible to reflect the same calculation on the sub/grand totals ?

erik_tarnvik

Established Member

Re: Condition in a measure - how to ignore blank nominator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2017
09:37 AM

Yes it is. You just need to change the sum formula to exclude months of 2016 where 2017 is blank. Like this:

YOYS = IF(ISBLANK(SUM(YOY[Yr_2017])) || ISBLANK(SUM(YOY[Yr_2016])), BLANK(), sum(YOY[Yr_2017]) / SUMX(FILTER(YOY, NOT(ISBLANK(YOY[Yr_2017]))),YOY[Yr_2016]) - 1)

katalay

Frequent Visitor

Re: Condition in a measure - how to ignore blank nominator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2017
11:45 AM

Thank you very much Erik!!

erik_tarnvik

Established Member

Re: Condition in a measure - how to ignore blank nominator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2017
02:45 PM

My pleasure @katalay.

katalay

Frequent Visitor

Re: Condition in a measure - how to ignore blank nominator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2017
04:41 PM

@erik_tarnvik I was applying the logic to a different column and I noticed the following.

The data set is actually at a more granular level (down to the city) so I believe when I use the sumx function some of the 2016 values isn't included in the new calculation. Example below:

ALL_2016 = sum(YOY[MKT_2016])

ALL_2016_NEW = if(isblank(sum(YOY[MKT_2017]))

,blank(),

sumX(filter(New_BI_YOY,not(isblank(YOY[MKT_2017]))),YOY[MKT_2016]))

The results are below:

Why do you think this may be happening?

Thanks again for the help, really appreciate it!

erik_tarnvik

Established Member

Re: Condition in a measure - how to ignore blank nominator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2017
08:26 AM

Hi @katalay,

you need to make a distinction between summing up 2016 for the purpose of generating a YoY percentage that excludes months of 2017 for which you do not yet have a value, and summing up to generate a true total of 2016. The measure ALL_2016_NEW will exclude all 2016 months where there are no values for the same month in 2017. That's how I understood you wanted the YoY percentage calculated?

katalay

Frequent Visitor

Re: Condition in a measure - how to ignore blank nominator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2017
08:45 AM

Hi @erik_tarnvik,

Yes that is correct. I think the number doesn't add up to be equal because the data is at a more granular level (in my case it is actually at the city level).

So let's say if I have a value for Berlin in June 2016 but not in June 2017, since we are aggregating it and June 2017 Berlin is blank it is automatically ignoring June 2016 Berlin.

Do you think the formula is ignoring those values?

So yes I am trying to figure out a way to ignore only the records where only 2017 months are blank and apply it to 2016 at the higher level.

I hope I could explain it clearly and thanks again for your time!

erik_tarnvik

Established Member

Re: Condition in a measure - how to ignore blank nominator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2017
11:24 AM

Sorry @katalay, ignore my previous answer, I did not fully understand your question. Yes, without having seen your actual dataset, I think you are right, you will lose the city level information if it contains blanks for 2017. But that is easy to fix, just adjust the formula as below. By checking the SUM(YOY[MKT_2017]) rather than just the column it will not return blank for months where you have data for some cities but not others and all values will then be included for that month.

ALL_2016 = sum(YOY[MKT_2016]) ALL_2016_NEW = if(isblank(sum(YOY[MKT_2017])),

blank(), sumX(filter(New_BI_YOY,not(isblank(SUM(YOY[MKT_2017])))),YOY[MKT_2016]))