Group By and Sum in a KPI

[Evan0211]

I have a strange issue. I have a table that contains information like this...

Record ID	Amount	OutLink
123	333	abc
123	333	zzz
456	500	tre

I have done a group by in the data load to Sum(Amount) as "Sum Amount" and did a Group By Record ID.

In a KPI, if put in [Sum Amount] it is null on the initial load, but when I click on a record in the table, it shows the correct amount in the KPI. If I change the KPI to Sum([Sum Amount]) it loads initially, but the value is off. For the example above, it would show 1,166 (it basically ignores my group by). Any ideas?

[hanna_choi]

Hello

Do you want to output like below?

I created a summary table, and connected the summary table and the source table with the record_ID field.

I hope it helps.

Best Regards.

hanna.choi

[anat · Accepted solution]

We should have some functions in the kpi or else values will be displayed once any selection. You can try like below

only([Sum Amount]) 

[Evan0211]

Thanks, but I am looking for 833 not 1,166. The 333 should not be double counted. 

[Evan0211]

Only() does not show the total onLoad, it shows the correct grouped by sum when an element is selected in the table. I need a sum of the grouped by totals on the initial load and then when you filter down, the KPI filters with it.

Edit: I think this actually does work, I just had the Only() on the KPI instead of in the data load.

 Thank you so much!

[tresesco]

@Evan0211 , so you don't want aggregation of repetitive amounts ? If so, you can try like :

Sum(Distinct Amount) as [Sum Amount]