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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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