How to calculate total distinct income

[sushma.sreella]

Hello All,

Here is the dataset and I would like to visualize the total distinct income generated for the distinct id’s w.r.t the site.

ID	status	income	site
123	failed	1000	Site A
123	paid	1000	Site A
456	paid	2000	Site B
678	paid	400	Site C

I tried sumOver function by calculating the sum(income) over ID and site but I wanted the distinct income.

So I calculated sumOver(max(income),[id,site]) but I also need to calculate the sum of total income for different sites month wise. Can anyone help me with this.

Thank you!!

[SeanBoon]

Hi,

Thanks for reaching out. Reviewing previous posts here, I found this one that may work for you.

SumOver -Summing distinct entries from a table with duplicate rows

Correct. Get the distinct customers with their salary, add a dummy column called “flag”. Let flag=1. Union this pre-aggregated data with the original table. In the original table, the dummy column “flag”=0. After then, the calculated field in above post should work. If you visualize the data with KPI, or a table without any dimension, you can avoid distinctcountover with avgif(salary, flag=1). If you visualize the data with dimensions in the visual, the distinctcountover can’t be avoided. Enj…

Best,
Sean

[robdhondt]

Hi @sushma.sreella -

Good news! We recently released a new feature that makes this easy. You can use a LAC-A function.

c_sum_income_LAC-A

// make income for id and site
max(income,[ID, site])

Result: