One to Many rollup calculations in pivot table

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.

Hey

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

Hello @ame54 !

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:

Here I have collapsed the row for Jan 2020 so it shows me only the total sales for Jan 2020 across all countries and customers
Screenshot 2023-09-15 100059

Here I collapsed on just Canada as a country to show all sales across Canadian customers.
Screenshot 2023-09-15 100121

Screenshot 2023-09-15 100137

1 Like

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.

Let me know if either of those work!

Hello @ame54, I will go ahead and mark my previous response as the solution for now. If you have any other questions feel free to post a new topic so it will show at the top of our activity log for faster response times. Thank you and good luck!

hey sorry for the late reply

but i still dont think this will resovle my issues as when placed in pivot table you will still have the same issues when rolling up levels and duplication of the values, and also an average will only work if you have an even tree where all has same amount of leafe nodes which is not the case here.

For now im not able to show in the same chart the ads rev and gms and breakthem down by Company Name so i have a seperate chart going down to lowest level and then another chart linked to a different dataset that removes the Company Name and sums the Company sales up to level of the NGO

Hi @ame54,

Have you tried using LAC-A to deduplicate the data at the analysis level? Create the following calculated fields and put them in your Value field well instead of what you have currently.

Total Country Sales:

sum(min({Country Sales}, [Country, Year, Month]))

Total Company Sales:

sum(min({Company Sales}, [Company, Year, Month]))

Percentage of Total:

{Total Company Sales}/{Total Country Sales}
1 Like