Total in KPI depend on dimension in table

[chloe]

Hi guys,

Please check my print screen below.
The first table is a table with the data without any manipulation.
In the second table I have a column with IF statement  :
IF(Main Account = Account , Campaign , Null() )
And based on this "if", the SUN(Revenue) changes from 300$ to 100$ (I'm filtering nulls out of the table therefore I'm getting only the 100$ row).

I need to show in KPI object the SUM(Revenue) as shown in the second table = 100$.

How can I do that? 

Thank,

[chrismarlow · Accepted solution]

Hi,

Feels like this should do it,

Sum(IF([Main Account] = Account , Revenue))

Not sure if it should need an AGGR, can you try?

Cheers,

Chris.

[chloe]

Hi Chris,

Thank you for your response. 

It's not working.
I tried :

Sum(IF([Main Account] = Account , Revenue))

and also:

Sum(aggr(Sum(IF([Main Account] = Account , Revenue),Campaign))

Any other idea? 🙂

[Digvijay_Singh]

not sure but the sample data you shared, the expression by  @chrismarlow looks okay

Pl share better sample if its not working.

[GabrielFonsecaz]

Your 'IF' expression set 'Null()' if the anwser is not the same in the column "Main Account" and "Acount".  It´s a syntax error. 


If( Truth you are searching for, If its Right Result, If it´s wrong result) 

I could not understand what you want with that ''IF'' expression. Maybe if you need to show the revenue in the front, you could use the a simple SUM in the KPI. 

Example: 

SUM({<"Main Account"="Account">}Revenue)

And the result '100' is right actually. 

[chloe]

Thank you all for your help.

I solved it using @chrismarlow idea but instead of AGGR by Campaign , I used Account : 

sum(aggr(sum(if([Main Account] = Account ,Revenue))),Account))

Thanks!! 🙂