Calculated measures

[Anonymous]

Hi guys,

it would be great if you could help me out with the following situation.

I have i.e. sales data of the years 2016, 2015 & 2014 in 2 different categories.

Now I want to calculate what their sales are going to be in 2017, based on a growth %.

I've attached a file as an example.

I need a function for the following 4 calculated measures

1 = * if Category = A, and 2014, 2015 & 2016 > 0 then AVG 3 years * 1.2

2 = * if Category = A, and revenue <0 in 2014 but > 0 in 2015,2016 - then 2016 * 1.2

3 = * if Category = A and revenue in 2016 <= 0, then show 0

4 = * if Category = A and revenue in 2015 <= 0 and revenue in 2016 > 0, then show avg 2014-2016

Could you guys help me out?

Cheers,

Mike

[OmarBenSalem · Accepted solution]

Based on your example data :

if(sum({<Category={'A'}>}[Revenue 2014])>0 and sum({<Category={'A'}>}[Revenue 2015])>0 and sum({<Category={'A'}>}[Revenue 2016])>0,

(sum({<Category={'A'}>}[Revenue 2014])+sum({<Category={'A'}>}[Revenue 2015])+sum({<Category={'A'}>}[Revenue 2016]))/3

,

if(sum({<Category={'A'}>}[Revenue 2014])<0 and sum({<Category={'A'}>}[Revenue 2015])>0 and sum({<Category={'A'}>}[Revenue 2016])>0,

sum({<Category={'A'}>}[Revenue 2016])*1.2,

if(sum({<Category={'A'}>}[Revenue 2016])<=0 ,0,

if(sum({<Category={'A'}>}[Revenue 2015])<=0 and sum({<Category={'A'}>}[Revenue 2016])>0,

(sum({<Category={'A'}>}[Revenue 2014])+sum({<Category={'A'}>}[Revenue 2016]))/2

)

)

)

)

[OmarBenSalem]

If your question has been answered, please don't forget to close the thread by marking the correct answer as so.

Thanks,

[Anonymous]

Thanks for your reply Omar! I was still testing haha. I will mark the correct answer if I have everything working!:)

Unfortunately, I'm still getting NULL values when I'm using the next part of your code.

(if(sum({<Category={'A'}>}[Revenue 2014])<0

and sum({<Category={'A'}>}[Revenue 2015])>0

and sum({<Category={'A'}>}[Revenue 2016])>0,

sum({<Category={'A'}>}[Revenue 2016])*1.2)

And what should I do if I want to add the same formulas for Category B to this formula, but with category B * 1.1?

[OmarBenSalem]

Can you share the qvf file (your app) you're working with?

[Anonymous]

I can't do that unfortunately, because the data that we use is confidential for our company..

What I'm searching for, is a formula that says what you already explained, but with a second if statement (if category = B). That could be the same formula, but I can't get the multiple if statements working.

Sorry for the inconvenience..

[OmarBenSalem]

You simply choose a chart,

as dimension: category:

as measure:

if(sum([Revenue 2014])>0 and sum([Revenue 2015])>0 and sum([Revenue 2016])>0,

(sum([Revenue 2014])+sum([Revenue 2015])+sum([Revenue 2016]))/3

,

if(sum([Revenue 2014])<0 and sum([Revenue 2015])>0 and sum([Revenue 2016])>0,

sum([Revenue 2016])*1.2,

if(sum([Revenue 2016])<=0 ,0,

if(sum([Revenue 2015])<=0 and sum([Revenue 2016])>0,

(sum([Revenue 2014])+sum([Revenue 2016]))/2

)

)

)

)

result :

I'm working with :