[Q] Enhancements of this formula? (use of filter function without specifying the value?)

[AnonymeC]

Hello,

Hope you can help me on this.

Here is an example of the database:

2 different type: Food/Non food

4 different sub types (Meat, Vegetables, Clothes, Electronics)

Article code	Type	Sub-Type	Brand	Sales	Custom column (result)
00001	Food	Meat	XXX	30	=30+50=80
00002	Food	Vegetables	XXX	40	70
00003	Non food	Clothes	XXX	10	115
....	Food	Meat	XXX	50	80
....	Non food	Clothes	XXX	15	115
....	Non food	Electronics	XXX	2	2
....	Non food	Clothes	XXX	90	115
00008	Food	Vegetables	XXX	30	70

My issue is the following. I am working on the Power Pivot editor and want to add a new custom column that I will call "result"). At the moment I did not manage to reach the wanted result, I am using a formula that calculates the sum of sales with a filter on a specific sub-type.

Current formula I am using is: = CALCULATE( SUM( My_Worksheet[Sales]), FILTER( My_Worksheet, My_Worksheet[Sub-Type]="Meat"))

Then I repeat the same formula for all different sub types, only changing the filter part ( My_Worksheet[Sub-Type]= "Vegetables", "Clothes" ...).

So I have several added columns, each for one specific sub type. Problem is it lacks flexibility, for example if there is a change of naming in the database and "Clothes" become "Cloth" then the formula won't work because I am calling a specific name. Is there a way to do this operation of sum per sub-types, without calling/filtering directly one Sub-Type?

Hope it is clear, many thanks in advance.

[AnonymeC]

To make the issue simpler:

What I have:

Code	Sub-type	Sales
1	Meat	10
2	Meat	50
3	Vegetables	30

What I want:

Code	Sub-type	Sales	Sales per sub type
1	Meat	10	60
2	Meat	50	60
3	Vegetables	30	30

(aim in the end is to calculate impact and weight of each sub-type)

[AnonymeC · Accepted solution]

I managed to find something that worked:

= CALCULATE( SUM( MyWorksheet[Sales]), ALLEXCEPT( MyWorksheet, MyWorksheet[Sub-Types]))

This gives the correct Sub total for each row (sales per sub type).