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
Anonymous
Not applicable

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
erik_tarnvik
Solution Specialist
Solution Specialist

 

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

Hi @Anonymous, 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
Solution Specialist
Solution Specialist

 

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

 

 

Anonymous
Not applicable

Thank you Erik that helped a lot. 

 

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

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)

 

Anonymous
Not applicable

Thank you very much Erik!!

My pleasure @Anonymous.

Anonymous
Not applicable

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

Hi @Anonymous,

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?

Anonymous
Not applicable

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!

Sorry @Anonymous, 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]))

 

Anonymous
Not applicable

@erik_tarnvik, I have just applied the new solution, but it looks like this time again the subtotal reverts back to the total (includes sum from the blanks). Example below.

 

Capture.PNG

 

Thanks again for all this help, I am new with DAX language and all this back and forth helps me to learn something new eery time!

Really appreciate it!

Hi @Anonymous, 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])
)
Anonymous
Not applicable

@erik_tarnvik, this worked great! 

 

Thank you for all the help, it was very good learning all of these!

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.