Forum Discussion

michael_ma's avatar
michael_ma
Frequent Visitor
2 years ago

All except function not working as expected

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 r...
sevenhills's avatar
sevenhills
Super User
2 years ago

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's avatar
michael_ma
Frequent Visitor
2 years ago

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's avatar
    sevenhills
    Super User
    2 years ago

    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!