Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
newpbiuser01
Helper V
Helper V

Calculating Percentage of Total Using Filter and AllSelected Function

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 StoreMondayAppleFruit5.00
Farmers MarketTuesdayCucumberVegetable2.00
Grocery StoreWednesdayOrangeFruit6.00
Farmers MarketMondayPeasVegetable8.00
Farmers MarketTuesdayBananaFruit1.00
Grocery StoreMondayPineappleFruit10.00
Grocery StoreMondayCarrotsVegetable2.00
Grocery StoreTuesdayCucumberVegetable0.75

 

I am trying to get something like this:

newpbiuser01_0-1703104902475.png

 

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:

newpbiuser01_1-1703104958789.png

 

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!

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

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:
Ritaf1983_0-1703130104684.png

pbix is attached

 If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
 
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

1 REPLY 1
Ritaf1983
Super User
Super User

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:
Ritaf1983_0-1703130104684.png

pbix is attached

 If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
 
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.