Measure to sum based on multiple category scenarios

[rpatel92]

Hello,

Trying to create a measure to sum based on the categories, but also looking to get it to add addtional amounts. I have an example of data below and the output I am looking for.

DATA

Sales
Category No	Category	Sales
1001	Cauliflower	1
1002	Orange	3
1003	Potato	4
1004	Apple	1
1005	Banana	4
1006	Onion	2
1001	Cauliflower	2
1002	Orange	7
1003	Potato	1
1004	Apple	2
1005	Banana	3
1006	Onion	2
1001	Cauliflower	1
1002	Orange	5
1003	Potato	3
1004	Apple	2
1005	Banana	3
1006	Onion	2

OUTPUT

Items
Category No	Category	Sum Amount
1001	Cauliflower	4
1002	Orange	15
1003	Potato	8
1004	Apple	5
1005	Banana	10
1006	Onion	6
1007	Vegetables	18
1008	Fruits	30

Basically looking to get the measure to SUM for Vegetables and Fruits, but I am unable to do so at the moment. Ofcourse, the actual data is more complex and can't sahre for obvious reasons. Below is what I have treid so far with not much luck.

Amount =

    VAR CAT = SELECTEDVALUE(CATEGORY)

    VAR TAMT= CALCULATE(sum(Sales))

    VAR VAMT = CALCULATE(sum(Sales),CATEGORY = "CAULIFLOWER"||CATEGORY = "POTATO"||CATEGORY = "ONION")

    VAR FAMT = CALCULATE(sum(Sales),CATEGORY = "ORANGE"||CATEGORY = "APPLE"||CATEGORY = "BANANA")

    VAR AMT = If(CAT = "VEGETABLE", VAMT,

                If(CAT = "FRUIT", FAMT,

                    TAMT))

    RETURN AMT

Appreciate any help that you provide. Thank you.

[sjoerdvn · Accepted solution]

For this, I would implement helper dimensions.
First, the one that is to be used in the final report visual

Master_categories

Category No	Category
1001	Cauliflower
1002	Orange
1003	Potato
1004	Apple
1005	Banana
1006	Onion
1007	Vegetables
1008	Fruits

Next, an intermediary one

Category No	Category	Master Category No
1001	Cauliflower	1007
1001	Cauliflower	1001
1002	Orange	1008
1002	Orange	1002
1003	Potato	1007
1003	Potato	1003
1004	Apple	1008
1004	Apple	1004
1005	Banana	1008
1005	Banana	1005
1006	Onion	1007
1006	Onion	1006
1007	Vegetables	1007
1008	Fruits	1008

Now create a bi-directional relationship between the master categories table and the intermediary categories table, and a single relationship between the intermediary table and the data table.
Now, in you visual you can select the columsn from the master categories table and the sum from the data table.

[Anonymous]

Hi @rpatel92 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[d_m_LNK]

You could create a calculated column to persist a new subcategory of Vegetable or Fruit to a new calculated column in your table.  You could then do two simple calculate measures for Fruits and vegetables.

Calculated Column could be:

Sales[Subcategory] = 

SWITCH(TRUE(),

Sales[CATEGORY] IN {"CAULIFLOWER", "POTATO", "ONION"}, "Vegetable",

Sales[Category] IN {"Orange", "Apple", "Banana"}, "Fruit", "Uncategorized"

)

Then your Measures could be

Vegetable Sum = Calculate(Sum(Sales), Sales[Subcategory] = "Vegetable")

Fruit Sum = Calculate(Sum(Sales), Sales[Subcategory] = "Fruit")

[rpatel92]

Thank you David, going to give that a try over the weekend and see if that works. Appreacite the input.