Total in Table chart do not compute all the measures

[jsjavier]

Hi.

Im having problem in computing all values defined in my measure. As you can see the 3rd column tells the number of  total bed capacity for each floor. If you add all the values in the 3rd column it will sum up to 520 but the total always shows 249. Attached is the If condition script I used in data load editor. The formula in my measure is just SUM(DISTINCT(flr_count)).

I hope someone can help me. thanks

[jonathandienst · Accepted solution]

The problem is that you are using distinct at different levels of granularity. The total looks at the distinct flr_count values regardless of the floor, so it will discount the duplicates even if they are on another floor. You will need and Aggr() to fix this. Assuming that  the only dimension in this table is flr_name, then try:

=Sum(Aggr(Sum(Distinct flr_count), flr_name))

[jsjavier]

Thank you very much sir!