Sumarization issue with DAX formula

[Rbakker888]

I am trying to make some visuals with a formula for revenue where I multiply the revenue by 10 times if the product is a contract, the issue I am having is that it seems to summarize all the revenue from all orders that have that product when one order has that revenue more then 1 time. I have tried using other things then SumX but I cant get it to work or really understand how its going wrong.

My formula for the multiplied revenue:

Order Marge Bedrag € Multiplied test =

SUMX(

    fact_Orders,

    IF (

        fact_Orders[ArtikelCategorie] = "Contract",

        fact_Orders[Order Marge Bedrag €] * 10,

        fact_Orders[Order Marge Bedrag €]

    )

)

My formula for the standard revenue that does work:

Order Marge Bedrag € = [Order Verkoop Bedrag €]-[Order Inkoop Bedrag €]
Just a simple sell - buy price

The sell and buy price columns are a simple Sum of those columns and are correct

Some sample data:

The important ones being: Order-ID, Artikelnummer(Articlenumber), Verkoop prijs(Sell price), Inkoop (Buy price)

This is the result I get from my formula, the right column is correct as you can see and the left one is with my formula but as you can see cause the product was bought 2 times on this order and so for some reason instead of calculating it just for 2 rows it does it for all, also the ones from different orders

[Deku]

This is like due to autoexist, please see this SQLBI video on the topic, and this blog post fro jeffery wang

In essence filters on the same table are grouped into tuples. When you remove a filter you remove from the tuple rather than the column, giving unexpected results

You have two options

• Adjust the summarizecolumn() behaviour, as per the article, to Independent 

• create a dimension for categories, and use this for your logic. Then the filter are coming from two different tables and autoexist doesn't kick in

[Rbakker888]

@Deku I tried this but changing the Value Filter behaviour doesnt seem to change anything and the second solution also does not seem to work or I am doing it wrong. For the second solution, I just tried to copy the table and then use the column there for the categories.

[Deku · Accepted solution]

Try the second solution and this

SUMX(

Values(Dimension[ArtikelCategorie]),

Calculate( sum(fact_Orders[Order Marge Bedrag €] )) *

 IF (

 Dimension[ArtikelCategorie] = "Contract", 10, 1)

)

[Rbakker888]

Due to some of how my semantic model is build I had to very slightly edit it but this got me the correct base, thank you.
My final formula:

SUMX(
VALUES(Dimension[ArtikelCategorie]),
CALCULATE( [Order Marge Bedrag €] ) *
IF( SELECTEDVALUE(Dimension[ArtikelCategorie]) = "Contract", 10, 1 )
)