Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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!
Yes, ALLSELECTED is going to be problematic in that case. Try going with ALL.
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.
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:
the black line should also be stepwise in stead of straight?
Not sure if I fully understand it though.
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |