topic Re: Total in KPI depend on dimension in table in App Development

Total in KPI depend on dimension in table

chloe — Thu, 17 Feb 2022 17:47:27 GMT

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,

Re: Total in KPI depend on dimension in table

chrismarlow — Thu, 17 Feb 2022 18:51:28 GMT

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.

Re: Total in KPI depend on dimension in table

chloe — Thu, 17 Feb 2022 19:02:47 GMT

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? 🙂

Re: Total in KPI depend on dimension in table

Digvijay_Singh — Thu, 17 Feb 2022 20:30:06 GMT

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

Pl share better sample if its not working.

Re: Total in KPI depend on dimension in table

GabrielFonsecaz — Thu, 17 Feb 2022 20:37:43 GMT

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.

Re: Total in KPI depend on dimension in table

chloe — Thu, 24 Feb 2022 08:12:01 GMT

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