Table A has the correct Sum(revenue), but I need another metric from table B to divide it to get penetration. However, when I left join on table A, I notice the sum(revenue) becoming exponentially large and I’m presuming because revenue is being duplicated when the join criteria satisfies it. Now my sum(revenue) is wrong though. In Tableau there is a blend function, so it’s simple to use, but In QS there isn’t a functionality like that so that is why I am doing a left join. Does anyone have an idea for me to get the correct sum(revenue) from Table A even when left joining with Table B? Any help or guidance would be appreciated!
@darcoli Hey Darren, I saw you help solve a problem on this forum. Are you able to provide some guidance for me? I’ve been stuck on this for a few days and it’s super frusturating.
Depending on how things are set up, the following calculated field might give you the correct revenue total: sum(revenue) / count(revenue). If it does not work please give some more details on the fields in both table (id’s, revenue groups etc…)
can you show me how the final visual is meant to look? (it’s ok if the numbers are not correct - i just want determine the right calculation for what you need)
Hello darcoli, thank you for the averaging out suggestion. I had a similar question to that of author. Your suggestion works for individual rows, but when I say “Show Total” for the table, the total row shows averaged value as well, instead of sum of all the individual row average. Is there a way to show average (no duplicates while getting sum) per row and Total row shows sum of all the individual values?
This works for my use case.
sum(spend, [account]) / count(spend, [account])