cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
katalay Frequent Visitor
Frequent Visitor

Condition in a measure - how to ignore blank nominator?

Hi, 

 

I am working on the matrix below:

Capture.PNG

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)

 

Capture2.png

 

Thank you for the help!

2 ACCEPTED SOLUTIONS

Accepted Solutions
erik_tarnvik Established Member
Established Member

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

 

YOY = IF(ISBLANK(sum(YOY[Yr_2017])) || ISBLANK(sum(YOY[Yr_2016])), 
BLANK(),
sum(YOY[Yr_2017]) / sum(YOY[Yr_2016]) -1)

 

 

View solution in original post

erik_tarnvik Established Member
Established Member

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

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])
)

View solution in original post

12 REPLIES 12
erik_tarnvik Established Member
Established Member

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

 

YOY = IF(ISBLANK(sum(YOY[Yr_2017])) || ISBLANK(sum(YOY[Yr_2016])), 
BLANK(),
sum(YOY[Yr_2017]) / sum(YOY[Yr_2016]) -1)

 

 

View solution in original post

katalay Frequent Visitor
Frequent Visitor

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

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
Established Member

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

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
Frequent Visitor

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

Thank you very much Erik!!

erik_tarnvik Established Member
Established Member

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

My pleasure @katalay.

Highlighted
katalay Frequent Visitor
Frequent Visitor

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

@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:

 

Capture.PNG

 

Why do you think this may be happening?

 

Thanks again for the help, really appreciate it!

erik_tarnvik Established Member
Established Member

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

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
Frequent Visitor

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

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
Established Member

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

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]))

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 147 members 1,561 guests
Please welcome our newest community members: