Hey im trying to figure calculation that has one to many and avoid duplicates when to rolling up
I have dataset that looks like this:
Year - Month - Country - Country Sales - Company - Company Sales
2023 - 06 - USA - 1000 - CA - 10
2023 - 06 - USA - 1000 - CB - 50
2023 - 06 - JAPAN - 500 - CA - 5
I want create pivot table with this data and add calculation Percent Company Sales from Country
Pivot levels: Year → Month → Country → Company → SUM(Country Sales) SUM(Company Sales) Sum(Company Sales) / Sum(Country Sales)
→
When Pivot is fully expanded to lowest level we will see data like this:
2023 - 06 - USA - CA - 1000 - 10 - (10/1000)
2023 - 06 - USA - CB - 1000 - 50 - (50/1000)
2023 - 06 - JAPAN - CA - 500 - 5 - (5/500)
When Pivot is at Country level expanded to lowest level we will see data like this:
2023 - 06 - USA - 1000 - 60 - (60/1000)
2023 - 06 - JAPAN - 500 - 5 - (5/500)
When Pivot is at Month level expanded to lowest level we will see data like this:
2023 - 06 - 1500 - 65 - (65/1500)
Hello @ame54, something that could be really helpful for this scenario would be the percentOfTotal calculation within QuickSight. I will include the documentation here. This would allow you to get the percent of the total sales based on the sales from each country. Let me know if this helps!
Hey thnx for the reply, but if i understood it correctly this will not work here.
percentOfTotal is for same measure split by a dimension ==> so sum(a)/ sum(a,dimension)
In this case i have measure that is defined at Country level being repeated per Company and a different measure defined at Company level
Hello @ame54, is your value for Company Sales and Country Sales coming from your dataset or a calculated field. I’m still not entirely sure why the percentOfTotal doesn’t work. I believe if you used percentOfTotal(sum({Company Sales}), [{Country Sales}]) would give you the values you are looking for. There would probably be an issue though if you are using Level Aware calculated fields to create those values.
In that case, I would create 2 calculated fields:
sumOver({Sales}, [Company], PRE_AGG)
sumOver({Sales}, [Country], PRE_AGG)
And then another field that will simply be the company calculated field divided by the country calculated field to get your percentages.
So the Company Sales & Country Sales are coming from my dataset, so the raw table i put as example at start is the data from the table directly .
Also the Company Sales & Country Sales are not related to each other, so the sum of all the Company Sales do not add up to the Country Sales, so using Country Sales value as a dimension here to get breakdown does not really mean much.
It is more that we know these Companies opperate in these countries and generate these sales, and also seperately we know these Countries generate Sales by themselves seperately unrelated to Sales of the Companies.
What we want to see is these Company sales how do they compare to the Country Sales.
Another way i can create sampel Dataset if the Sales part is confusing
Is like this:
Year - Month - Country - NGO - NGO Revenue - Company - Company Sales
2023 - 06 - USA - NGOUSA - 1000 - CA - 10
2023 - 06 - USA - NGOUSA - 1000 - CB - 50
2023 - 06 - JAPAN - NGOJP - 500 - CA - 5
And i want to compare Sales of Companies to Revenue of NGOS, here per Country i only have one NGO but i have multiple Companies.
In a regular BI tool i would simple have a data model where:
Table NGO:
Year - Month - NGO name - NGO Sales
Table Company:
Year - Month - Company name - Company Sales
Bridge Table:
Year - Month - NGO Name - Company Name
And then i would not have these duplicated values issues as they would be seperate, but since QS can only take one table as far as my knowledge goes i will have to duplicate the NGO values to be able to mix these two seperate facts together
I think most of this could be acheived by row placement in the Rows Field Well. For example, I have date field set to month at the top, and then levels of granularity set below so that the pivot table expands in that order:
Hey
Not sure if i understood you right, but if you mean just to plug fields as in in a pivot table that wont work.
The issue here is again that Contry Sales /NGO Revenue are duplicated becuase i want to mix two DIFFERENT facts together.
If i direclty place them in pivot i ghet issues im facing of duplicating values.
For example based on dataset i shared i would have at NGOUSD level 2000 while it should be 1000
and at Month level i get 2500 and it should be 1500
Hello @ame54, this last screenshot was very helpful, I see the issue you are facing now. What if instead of using the NGO Revenue field directly from the dataset, you instead created a calculated field that looks like this: sumOver({NGO Revenue}, [NGO Name], PRE_AGG)
Then import that into your field well on the table instead of the field from the dataset.
Another possibility would be changing the aggregation on the Sum of NGO Revenue field to an average instead of SUM. Since the NGO Revenue will be the same on every row it shares, it might just return the single value for that date aggregation.
