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
gluizqueiroz
Resolver I
Resolver I

Measure with ALLEXCEPT ignore including date filters, how to ignore just the value that I put on AEx

I have created a measure to SUM the TotalValue per Region but agreggate by SUM the regions with the same Indicator.

 

MeasureWithProblem = CALCULATE(SUM(Table[TotalSale]); ALLEXCEPT(Table; Table[Indicator]))


Like the following image:

image.png

The measure really SUM the TotalValues when Indicator is equal, but if select one month on slicer the measure ignore the month too, like the following:

image.png
As you can see, the "MeasureWithProblem" always returns 255 for East and West when the correct is 85 when "feb" is selected.


Here a sample of data:

RegionTotalSaleIndicatorMonth
South100jan
North151jan
West202jan
East252jan
South300feb
North351feb
West402feb
East452feb
South500mar
North551mar
West602mar
East652mar
3 REPLIES 3
jdbuchanan71
Super User
Super User

Hello @gluizqueiroz 

ALLEXCEPT(Table; Table[Indicator]))

Tells DAX to remove the filters from every column of Table except [indicator] so it is removing the filter on data even when you slice on a month.  Because the indicator is in your visual you should be able to use just

SUM(Table[TotalSale])

 and get the anwer you are looking for.

Hey @jdbuchanan71 

I already tried the simple SUM and it doesn't agreggate by sum where the Indicator column is equal

@gluizqueiroz So I guess you want to sym by indicator regardless the region difference right? If yes, try this one:

 

measureByRegionIndicator = CALCULATE(SUM('Table'[TotalSale]),ALLEXCEPT('Table','Table'[Indicator],'Table'[Month]))

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.