Calculating Percentage of Total Using Filter and AllSelected Function

[newpbiuser01]

Hello,

I have a data table that looks like the following table. I am trying to create a bar chart that show the % Spend by Date and Type of Produce while filtering the table for Location = Grocery Store  and Day = Monday or Tuesday.

LocationDayProduceTypePrice

Grocery Store	Monday	Apple	Fruit	5.00
Farmers Market	Tuesday	Cucumber	Vegetable	2.00
Grocery Store	Wednesday	Orange	Fruit	6.00
Farmers Market	Monday	Peas	Vegetable	8.00
Farmers Market	Tuesday	Banana	Fruit	1.00
Grocery Store	Monday	Pineapple	Fruit	10.00
Grocery Store	Monday	Carrots	Vegetable	2.00
Grocery Store	Tuesday	Cucumber	Vegetable	0.75

I am trying to get something like this:

However, when I try to do it in DAX using the following expression: 

% Spend with Filters =

CALCULATE(SUM('Table'[Price]), FILTER('Table', [Day] = "Monday" || [Day] = "Tuesday"), FILTER('Table', [Location] = "Grocery Store"))

/CALCULATE(SUM('Table'[Price]), FILTER('Table', [Day] = "Monday" || [Day] = "Tuesday"), FILTER('Table', [Location] = "Grocery Store"), ALLSELECTED('Table'[Type]))

I get the following result:

What am I doing wrong? I am able to apply multiple filters within the same expression, why does adding "AllSelected" break it? How can I calculate the % of the total while filtering the data?

Any help would be greatly appreciated!

[Ritaf1983 · Accepted solution]

Hi @newpbiuser01 
Please use these measures

_fruits =

Var sum_fruits = CALCULATE(sum('Table'[Price]),'Table'[Location]="Grocery Store" && 'Table'[Type]="Fruit")

Var all_prod = CALCULATE(sum('Table'[Price]),'Table'[Location]="Grocery Store" )

RETURN

if (max('Table'[Day]) = "Monday" ||max('Table'[Day]) = "Tuesday" ,

sum_fruits/all_prod,BLANK())

_veg =

Var sum_fruits = CALCULATE(sum('Table'[Price]),'Table'[Location]="Grocery Store" && 'Table'[Type]="Vegetable")

Var all_prod = CALCULATE(sum('Table'[Price]),'Table'[Location]="Grocery Store" )

RETURN

if (max('Table'[Day]) = "Monday" ||max('Table'[Day]) = "Tuesday" ,

sum_fruits/all_prod,BLANK())

RESULT:

pbix is attached

 If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly