I have a dataset consisting of total weight, total recycling weight, and rate of recycling.
I am trying to create a calculated field, where I want to show a donut chart showing the total weight being split by total recycling weight and non-recycling weight.
I have 3 conditions which I am trying to satisfy:
1.) If the total weight = total recycling weight (i.e., rate of recycling is 100%) then show the total weight
2.) if the total weight <> total recycling weight (i.e., rate of recycling is > 0% and < 100%) then show the total recycling weight BUT within this loop only I want it to the subtracted weight as âNon-Recycling Weightâ. (i.e., if rate of recycling is > 0% and < 100%, example: rate of recycling is 25% and total weight is 100kgs then total recycling weight = 25kgs, and I also want it to output 100-25 = 75 kgs as âNon Recycling Weightâ. So that I can create my donut chart.
If I understand the requirement correctly, you have rate of recycling in percentage and total weight as absolute value and you want to put up a distribution chart in absolute values for recycling weight and non recycling weight.
For this use case we can create a gauge chart and change the arc style to 360. Total weight is the target and calculated recycling weight is the value.
The only challenge is you cannot add data label for the difference value - non recyclable. But it can be added as Tooltip if required.
If this is not what you are looking for , please add more details on what data is available and what you want to achieve.
Thank you for providing a solution to this requirement. I like the idea of using a gauge chart. However, my requirement is to use a donut chart with actual split of recycling and non-recycling.
I have attached a snippet of dataset for your reference. Hope it helps.
The columns include - Total Weight (kg), Total Recycling Weight (kg) (which is nothing but Total Weight (kg) * Recycling (%)), and Recycling (%).
If you see row 2 - Total Weight (kg) = 2066.14, Recycling (%) = 0%, so Total Recycling Weight (kg) = 0, which should ideally return me total weight using the ifelse condition â ifelse(Total Weight (kg) <> Total Recycling Weight (kg) AND Recycling (%) = 0%, Total Weight (kg)) â which I would create a new calculated field and name it as âNon-Recyclingâ.
But if you look at row 12 - Total Weight (kg) = 11402.87, Recycling (%) = 50%, so Total Recycling Weight (kg) = 5701.44, which should return the 5701.44 as âRecyclingâ and in the same condition the remaining that is 11402.87 - 5701.44 = 5701.44 as âNon-Recyclingâ
Additionally I also created a snippet of what I exactly I need and having trouble computing. The ones in green I was able to compute and the one in red is something I am facing challenge computing.
I understand your intent to break down a record which has non zero recycling % to 2 products Recycled and non-recycled. Since the condition 2 and 3 in if clause is same, the first one satisfies and the remaining weight is not accounted in non-recyclable weight.
There is no way to fan out the data at recycled/non-recycled level. All calculation here follow the same rule of data manipulation language, record level calculations.
You can do this in the back end while ingesting data, to create a field and calculate non-recycled weight and then unpivot the columns. However, we cannot do that after ingesting data in QuickSight as of date.
If you want to proceed with the existing format of data, then as mentioned earlier gauge chart is the only option I see.
Hi @akshaym,
Itâs been awhile since we last heard from you, did you have any additional questions regarding your initial post?
If we do not hear back within the next 3 business days, Iâll go ahead and close out this topic.
I have been yet struggling with this requirement as I do not know how to arrive at the desired result. The work around @prantika_sinha suggested does not help my use case.
If anyone else has a better approach that I can implement to arrive at the desired result, that would be helpful.
Hi @akshaym,
What from the suggestion above did not work for you? As Prantika mentioned, unfortunately thereâs no out of the box way to compile the data as in your case; did you try making alterations to the dataset?
Hi @akshaym,
Itâs been awhile since we last heard from you, do you still need further assistance on this topic and if so, please refer to the questions I raised above.
If we do not hear back within the next 3 business days, Iâll go ahead and close out this topic.
Hi @akshaym,
Since we havenât heard back, Iâll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
