All except function not working as expected

[michael_ma]

Hi Everyone, 

I'm currently trying to migrate over a report from Tableau to Power BI but having a bit of trouble understanding what's going on with the All Except function. 

This is my desired result taken from Tableau :

However, when I try and reproduce the visual in Power BI, this is the result I'm getting:

My measure is for Royal Moore Invoice Net Amt is as follows: 

The customers and transactions tables have a 1: many relationship. I'm guessing what's happening here is that the all except function is trying to filter the customer table, but doesn't capture the data in the transactions table. I'm sure that if I create a denormalized view of the transactions and customer table I can achieve the desired result, but that doesn't seem like a long term practical solution. I'm wondreing if anyone has any other idea where I can keep the star schema structure of my data model intact, but still achieve my desired result. 

[sevenhills]

Try this and see if it works

Royal Moore Invoice Net Amt =
CALCULATE (
    SUM ( Transactions[INVOICE_NET_AMOUNT] * -1 ),
    ALLSELECTED ( Transactions ),
    VALUES ( Transactions[Top ... Number] )
)

[michael_ma]

The problem is that the top level parent customer number field comes from the customer table and not the transactions table. In order to make your solution work I would have to add top level parent customer number field to the transactions table. I'm sure if I do that your soultion will work, but adding a text field to my fact table doesn't seem like a good idea

[sevenhills]

My bad! I missed that one! Try this!

Measure DAX

Royal Moore Invoice Net Amt =
CALCULATE (
SUM ( Transactions[INVOICE_NET_AMOUNT] * -1 ),
ALLSELECTED ( Transactions ),
VALUES ( Customers[Top ... Number] )
)

You can write also as

CALCULATE(
SUM('Transactions'[INVOICE_NET_AMOUNT] * -1),
ALLSELECTED(Transactions),
Filter(
all('Customers'),
Customers[Top ... Number] = SELECTEDVALUE(Customers[Top ... Number])
)
)

and for line sum with other columns, you can try as 

CALCULATE(
SUM('Transactions'[INVOICE_NET_AMOUNT] * -1),
Filter( all('Customers'),
Customers[Top ... Number] = SELECTEDVALUE(Customers[Top ... Number])
)
)

Hope it helps!