Here is a snippet of my mocked up data.
Table A (flat file) = Goal numbers assigned to individual users
Table B = a sql table tracking actual YTD data wrt to the goal
Problem Statement: When I left join (Table B ON Table A) to calculate goal vs. actual attainment my goal number is getting duplicated. For example, User A who worked on 2 opportunities YTD has a goal of 10 but when I join and calculate attainment % it’s becoming 20. Am I missing something here? I tried using avg(2022 Goal) to get correct number but average doesn’t work when I aggregate data on different levels. Appreciate any input/feedback. TIA!
You are not doing anything wrong but this is how left joins work - all rows in table A get duplicated for all rows in table B that satisfy the join criteria.
I suppose you want to get the 2022 Goal in a way that summing all values will counter this duplication. As you said, one way to do that is to do an average per user… but you cannot use AVG() because we want a fixed aggregation (per user name). You can do this as follows:
which will divide the 2022 Goal for each user by the number of opportunities that that user has. This will then give the correct sum when summed up per user or when getting the overall sum for all users… but might not work for all use cases - let me know if this is what you want
Thank you for the solution! It worked exactly like I wanted it to. However, when I show the data in a pivot table is it possible for 2022 Goal & YTD goal (both are calculated fields) to show sum instead of performing calculation? Thanks!
Unfortunately QuickSight does not allow overriding the calculation for the subtotals. So we need to work around that by coming up with a calculation that gives the right answer both on a user aggregation level and overall … to get the right sum.
Due to this being a workaround of a workaround… it gets a bit hairy from here on… but hey it works
Try using the following in a calculated field in and adding it as a value in your pivot table:
