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.
YOY = IF(ISBLANK(sum(YOY[Yr_2017])) || ISBLANK(sum(YOY[Yr_2016])),
BLANK(),
sum(YOY[Yr_2017]) / sum(YOY[Yr_2016]) -1)
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]) )
YOY = IF(ISBLANK(sum(YOY[Yr_2017])) || ISBLANK(sum(YOY[Yr_2016])),
BLANK(),
sum(YOY[Yr_2017]) / sum(YOY[Yr_2016]) -1)
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)
Thank you very much Erik!!
My pleasure @katalay.
@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!
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?
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 @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]))
User | Count |
---|---|
127 | |
81 | |
75 | |
68 | |
67 |
User | Count |
---|---|
124 | |
74 | |
69 | |
58 | |
51 |