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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
kvbe
Frequent Visitor

Calculation not correct when using a visual filter

Probably something pretty easy but I cannot really figure out what to use and if it is possible.

I have a visual which shows my ordertotal/sales/targets. 

 

That works nicely but I added a visual filter to the graph to only show business days. 

I realized that some orders are raised on holidays and saw a mismatch between my numbers. 

 

mismatch.png

 

My order measure is as follow:

 

RunningTotalOrders = IF(LASTDATE(zz_Dim_Date[Calendar_Date]) > TODAY(), BLANK(),CALCULATE(sum(OrdersRaised[OrderTotal]),FILTER(ALLSELECTED(zz_Dim_Date),zz_Dim_Date[Calendar_Date] <= MAX(zz_Dim_Date[Calendar_Date]))))

It must be something with the ALLSELECTED() but not sure if I can just eliminate that one visual filter from my calculation so that it shows the right final value even though I only "show visually" the business days. 

 

Thanks!

4 REPLIES 4
Greg_Deckler
Community Champion
Community Champion

Yes, ALLSELECTED is going to be problematic in that case. Try going with ALL.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Problem is that when using ALL() I get a straight line for the line graph. It basically gives me the sum of all the data in the table as nothing is selected. I would like that I have something link ALLSLICERSBUTNOTVISUALFILTERS()  as function if you know what I mean. Would there be a workaround for this scenario?

Oh yeah, try ALLEXCEPT so that you can keep your slicer settings. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hmm. Trying ALLEXCEPT() does not really work with the filter I am useing. Not sure why.

When I change the measure to this:

 

RunningTotalOrders = IF(LASTDATE(zz_Dim_Date[Calendar_Date]) > TODAY(), BLANK(),CALCULATE(sum(OrdersRaised[OrderTotal]),ALLEXCEPT(zz_Dim_Date,zz_Dim_Date[Calendar_Year],zz_Dim_Date[Month_Name])))

Then it gives me the correct "value" but it does not work as a running total anymore as I cannot use the extra filter parameter anymore. 

 

zz_Dim_Date[Calendar_Date] <= MAX(zz_Dim_Date[Calendar_Date])

So if use the first ALLEXCEPT() then I get this as result:

 

straightline.png

 

the black line should also be stepwise in stead of straight?

Not sure if I fully understand it though.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.