cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bitbit
Helper I
Helper I

DAX formula to combine "Filter" and "ALL"

Hi. My table "data" in Power BI captures data for all 12 months for budget and 9 months of actual sales.  My Power BI report has a page filter set for "month".  I would like to create a measure in Power BI desktop which can generate the total year budget sales without having to remove the page filter.

This is what I had created for the measure:

Measure = calculate(sum(data[Sales]),data[version]="Budget",all(data[month]))

The result however only captures 9 months' of budget sales. Pls advise how I should amend the DAX formula.  Thank you

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @bitbit,

 

You can create a measure as below.

 

total year budget sales = CALCULATE(SUM(data[Sales]),FILTER(ALLEXCEPT(data,data[Country]),data[version]="Budget"))

Capture.PNG

 

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

8 REPLIES 8
v-frfei-msft
Community Support
Community Support

Hi @bitbit,

 

You can create a measure as below.

 

total year budget sales = CALCULATE(SUM(data[Sales]),FILTER(ALLEXCEPT(data,data[Country]),data[version]="Budget"))

Capture.PNG

 

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Hi Frank.  Solution works, many thanks!

v-frfei-msft
Community Support
Community Support

Hi @bitbit,

 

Please try to use this formual to create a measure. If it doesn't meet your requirement, Kindly share your pbix to me.

 

Measure = calculate(sum(data[Sales]),Filter(all(data),data[version]="Budget"))

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi.  Pls advise how I can obtain total year budget sales for each country in my sample pbix report (below url) without having to adjust the page filter.

https://www.dropbox.com/s/v1zv1hycllbeqco/test.pbix?dl=0

AkhilAshok
Solution Sage
Solution Sage

You basically have to clear all filters which are dependant on Sales, not just Date.

Anonymous
Not applicable

Try this:

Measure = calculate(sum(data[Sales]),data[version]="Budget",all(data))

 

Tell us if works for you.

 

Hi, Miltinho.  My table includes data breakdown into various sales area, by changing the formula, I can no longer get the total for each sales area which I need for other BI charts.

Anonymous
Not applicable

Hi bitbit,
It's better if you include a file with sample data with what you have to help you in a right way.

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors