Forum Discussion
Sumarization issue with DAX formula
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:
My formula for the standard revenue that does work:
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
Try the second solution and this
SUMX(
Values(Dimension[ArtikelCategorie]),
Calculate( sum(fact_Orders[Order Marge Bedrag €] )) *
IF (
Dimension[ArtikelCategorie] = "Contract", 10, 1)
)
4 Replies
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
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.
Try the second solution and this
SUMX(
Values(Dimension[ArtikelCategorie]),
Calculate( sum(fact_Orders[Order Marge Bedrag €] )) *
IF (
Dimension[ArtikelCategorie] = "Contract", 10, 1)
)
