Multiplying based on category

[Rbakker888]

I think I might be overlooking something simple here but I want to multiply my total revenue by x10 if the category of that order is "contract"

This is what I currently use but while this works when I have the category's seperated, once I put them all in 1 visual for example a bar chart and want to just have a slicer on the side with the category's it will do everything x10 cause it ofcourse contains an order thats an contract but i hope you understand I dont want that and want it to simply only multiply the revenue created by the contract.

The other category's should just display the standard revenue as shown in the DAX formula I used.

[rajendraongole1 · Accepted solution]

Hi @Rbakker888  - check the below modified measure:

Total_Revenue_Adjusted =
SUMX(
Fact_Orders,
IF(
Fact_Orders[OrderCategory] = "Contract",
Fact_Orders[OrderRevenue] * 10,
Fact_Orders[OrderRevenue]
)
)

Hope this works. 

[Rbakker888]

Ended up fixing my semantic model due to being granted access to it. This formula works now, thank you!

[Rbakker888]

Would there also be a way to do this without referencing the entire table as there is currently 1 faulty column in it and by referencing it, it doesnt work. I am to fix this column but need another IT guy for the permission to the database for it.

[rajendraongole1]

Yes! You can avoid referencing the entire table by using a CALCULATE function and applying a filter on OrderCategory.

Total_Revenue_Adjusted =
VAR RevenueContract =
CALCULATE(
SUM(Fact_Orders[OrderRevenue]),
Fact_Orders[OrderCategory] = "Contract"
) * 10

VAR RevenueOther =
CALCULATE(
SUM(Fact_Orders[OrderRevenue]),
Fact_Orders[OrderCategory] <> "Contract"
)

RETURN
RevenueContract + RevenueOther

This should work even if a column is causing issues in the table. 

[Rbakker888]

Your formula seems to have just put them all together, what you can see on this image is the top row which is related to a employee and the total amount of money they did orders for and below I want to see those numbers split up into the different categories. The left column is the base revenue which needs to be multiplied for the contract and the right column is the output of your formula.

[bhanu_gautam]

@Rbakker888 , Try using

Order Marge Bedrag Multiplied =
IF (
fact_Orders[Artikel Categorie] = "Contract",
fact_Orders[Order Marge Bedrag €] * 10,
fact_Orders[Order Marge Bedrag €]
)

[Rbakker888]

This doesnt work due to the [artikel Catergorie] being a column connected from a semantic model, so I cannot reference to it when using this query.