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…)
I am left joining Table B on Table A, on monthending, marketplace id, and gl_product. I am doing sum(GMS_USD)
Table B
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])