Sales Comparison Calculation Logic In Quicksight

Question & Answer
,
1

Good day !! I have a use case where I need to compare sales amount of different categories against sales of a particular category and it should be interacting with the Slicers present in the analysis as well.

For Example, I have a Category column comprising values like Furniture, Technology, Finance etc. I have couple of other columns like Region (having values like East, West, Central etc.) and Sales amount.

There are couple of slicers in the analysis - one for choosing category and other for choosing Region

The data table contains only 3 columns Category, Sales and Furniture Sales. The expectation if when the user will choose a category (say Technology) and Region (East and Central) the Table will show Total Technology Sales (“Technology” is chosen from Category Slicer and Sales are comprising of East and Central which are chosen from Region Slicer) and Total furniture Sales (“Furniture” is pre-decided and fixed and Sales are comprising of East and Central which are chosen from Region Slicer)

However, I am unable to achieve this. I am providing the calculation of the Furniture Sales column and other related details in the snapshot below for better understanding.

image
image2279×1089 328 KB

Looking forward to response, suggestions and guidance from all the Quicksight LAA / LAC Experts. Thank you!

2

Can you tell me why summing (instead of min) the furniture sales doesn’t work?

3

Hello @Max , For LAC-W functions, the visual aggregation defaults to MIN to eliminate duplicates. If it is changed to SUM the value will increase multi-fold due to the consideration of duplicates value. It is highlighted in the following link as well.

I am also sharing the snapshot of the values (which are obviously wrong) when I change it to SUM for reference

image
image971×627 108 KB

4

ahh, in your calculated field can you add category as a partition?

[Region, Category]

5

That will make the Furniture Sales Value zero as the Category Chosen is Technology and Fixed Category in the expression is Furniture. Hence, due to this contradictory criteria the value is becoming 0 (zero).

image
image976×705 120 KB

6

Do you need the ifelse then? If you partition by category it will take into the category and sum over that.