Dynamic total filtering on 2 dimensions

[Anonymous]

Hi Guys,

My data structure is made up of a fact table and several dimensions that join to it, see diagram below. The problem comes when I'm trying to create a dynamic total of the Order Value field in the Order Detail table, that takes into account any filters that might be applied to the Calendar and the Branch table. In other words I need a total Order Value calculated from my fact table but I want it NOT to ignore ANY filters applied to those tables. I know you can't do an ALLEXCEPT on a whole table, but I also can't anticipate which fields my users might use from these dimensions therefore I can't list them all individually in multiple ALLEXCEPT-s. So this is where I'm getting stuck. 

I thought the below would work: 

Dynamic Total = CALCULATE(sum('Order Detail Measures'[Order Value]),ALLEXCEPT('Calendar Date','Calendar Date'[Calendar ID]),ALLEXCEPT('Booking Branch','Booking Branch'[Booking Branch Key])) 

I also tried this: 

CALCULATE(sum('Order Detail Measures'[Daily Net Order Line Value]),ALLEXCEPT('Order Detail Measures','Calendar Date'[Calendar ID],'Order Detail Measures'[Booking Branch Key]))

As per my logic - which is probably wrong so please educate me on this! - because I'm referencing the Calendar ID and the Booking Branch Key fields in my ALLEXCEPT (these are the fields my fact uses to join to the relevant dimension tables) when the user filters on let's say Financial Year from the Calendar, the Calendar ID gets a context filter and therefore should affect my dyanmic total creating a total order value for the financial year selected. This is not what happens though, whenever I filter on a field that's  NOT Calendar ID from my Calendar table the calculation ignores my filter selection and only calculates correctly when I apply filters on Calendar ID or Branch Key. If I swap Calendar ID to Financial Year in the above 2 expressions, they work as expected but this isn't the goal, I do not want to restrict the end user to have to use specific fields from the Calendar or the Branch table, they should be able to use any. 

Any suggestions on what I'm doing wrong? 

Hope this all made sense.

Thank you 

[Anonymous]

Hi @Anonymous 

I build a sample to have a test by your measure.

Dynamic Total =
CALCULATE (
    SUM ( 'Order Detail Measures'[Order Value] ),
    ALLEXCEPT ( 'Calendar Date', 'Calendar Date'[Calendar ID] ),
    ALLEXCEPT ( 'Booking Branch', 'Booking Branch'[Booking Branch Key] )
)

My sample:

Your Measure with Allexcept function only contains Calendar ID Field in Calendar Date and Booking Branch key Field in Booking Branch. So this measure could only be impact when we filter  Calendar ID Field or Branch key Field. (I use the filter levels on this page ).

EX: When you filter Calendar Date Table by Calendar Date Field or Company Field, the result won't be impact and still show sum all.

So I think you can add Specific Fields you want to impact this measure in ALLEXPECT Function.

EX:

Dynamic Total =
CALCULATE (
    SUM ( 'Order Detail Measures'[Order Value] ),
    ALLEXCEPT ( 'Calendar Date', 'Calendar Date'[Calendar ID], 'Calendar Date'[Calendar Company]),
    ALLEXCEPT ( 'Booking Branch', 'Booking Branch'[Booking Branch Key] )
)

Or IF you want All fileds in Calendar Date Table and Booking Branch Table can filter this Measure.

You can use sum without allexcept function.

EX:

Measure 2 = sum('Order Detail Measures'[Order Value])

IF you want most fields can filter this Measure, and only a little bit fiels can't impact this measure.

Ex: Calendar ID and company can filter this measure and Date can't. You may try all function.

Dynamic Total = 
CALCULATE (
    SUM ( 'Order Detail Measures'[Order Value] ),
    ALL('Calendar Date'[Calendar Date]),
    ALLEXCEPT ( 'Booking Branch', 'Booking Branch'[Booking Branch Key] )
)

I think this blog Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT may help you to understand allxxx function in power bi.

You can download the pbix file from this link: Dynamic total filtering on 2 dimensions

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous · Accepted solution]

Hi Rico,

I managed to figure out how to make it work, just for reference this is what I was after: 

CALCULATE(sum('Order Detail Measures'[Order Value]),ALLSELECTED('Calendar Date'),ALLSELECTED('Booking Branch'))

This now dynamically returns the overall sum of Order Value but alllows to be filtered by any of the fields in Booking Branch or Calendar Date! 

[Anonymous]

Hi Rico,

Thank you for your detailed answer! 

Unfortunately the functionality I'm after is none of these scenarios. 

The below measure you're proposing for allowing filtering on both Branch and Calendar will not give me what I want, it'll just sum the Order Value up like a base measure. 

sum('Order Detail Measures'[Order Value])

Let's suppose the total Order Value for 2019 is £5m and for 2020 it's £2m, what I want is my calculation to always return £5m or £2m but not only when the person selects the Year field in the calendar, but using any of the other fields. But I don't want any of the other dimension tables to affect it. 

I don't think ALL or ALLEXCEPT is able to do what I'm after on its own. I will try and see if I can get to what I need using a Group by instead. 

Thank you