cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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


@ 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!:
Mastering Power BI 2nd Edition

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. 


@ 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!:
Mastering Power BI 2nd Edition

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors