I find some solutions from others’ topic (Removing Duplicates after joining two data sources), but it (sum(x) / count(x)) did not solve my problem.
I need all the 6 columns in my final table, including questions, because ditrict, unit, questions are customed filters for users in single dashboard. Now I need to calculate the correct total receivers per unit or per district. In this example, the total receivers for U1 is (150+100=250), total receivers for D1 is 450(150+100+200), I also need to calculate the average value per unit and per district (it is correctly calculated in this scenario so we do not discuss average value here). So my problem is how to correct calculate the total receivers after joining table A and B where contains duplicate.
In Tableau there is a blend function, so it’s simple to use; In Looker there is a sql_distinct_key to solve it, but In QS there isn’t a functionality like that.
- I tried to use table A and table B as two seperate datasets, and use parameters to control the common filter (like district, unit), but it does not work for ‘question’ filter.
- I tried to create calculated field to fix the duplicates, but sum(receivers)/count(*) does not make sence.
It is really tricky.Any help or guidance would be appreciated!