Display sales qty only once, instead of summing it up

[akshaym]

Hi,

I am currently facing a challenge where I want to display the sales qty only once, but due to the sales qty having multiple row which display the same qty across multiple rows, quicksight is summing up these values. I want to display using a bar chart

Thanks

[David_Wong]

Hi @akshaym,

What do you want to group by in your bar chart? Product Type? If you show the min/max/avg of Sales Qty and group by Product Type or Brand, you should get what you’re looking for.

[akshaym]

Hi @David_Wong,

Thank you for your reply. I guess I framed my question wrong, apologize for the inconvenience.

So, the way my graphs are structured is that, I have 1.) Which shows all my brands 2.) Shows the category and product type. 3.) Now this one should basically give me the overall quantity for say when I click on Category is Pizza so total sales are 63,688 and for Burger 392,616 and when I click on Product Type Cheese Pizza - 11,688.

Hope it helps.

Thanks,

[David_Wong]

Can you try this calculated field?

sum(min({Sales Qty}, [{Product Type}]))

[akshaym]

It doesn’t it just returns the minimum value which in the example above - 11688 and 12120

[David_Wong]

What field are you putting in your bar chart? Category or Product Type?

[akshaym]

I have the Category and Product Type in a Pivot table and bar chart for the Brands. Now similarly I want to display the qty as bar chart, which will show me the aggregate values whenever I click on the Product field in the Pivot table

[David_Wong]

Can you show screenshots of your visuals so that I can see where you’re not getting the expected results?

I created a pivot table and a bar chart using the above calculated field, and the numbers look correct to me. Are these numbers not what you’re expecting?

I added an action to the pivot table, and when I click on a product type, the same number shows in my bar chart.

[akshaym]

Hi @David_Wong,

I was able to get the desired results thank you for helping out. I also found a similar post.

Getting sum subtotals after using min as the level of aggregation

hi, I want to get the subtotals of my data using a pivot table after selecting min as the level of aggregation. However, whenever I do this, the subtotals become 0. For example, on the table below, I want the subtotals for A and B but before I can do that, I need to get rid of the duplicate row that contains Spain so I selected min as the level of aggregation for price (if I choose sum the value for Spain becomes $100 which is wrong). Selecting min however does not let me get the right type of…

Appreciate your help.

Thanks,