cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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
Super User
Super User

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

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.

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

Hey @jdbuchanan71 

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

mochabits Regular Visitor
Regular Visitor

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

@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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 109 members 1,495 guests
Please welcome our newest community members: