Forum Discussion
Multiplying based on category
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.
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.
7 Replies
Rbakker888 , Try using
Order Marge Bedrag Multiplied =
IF (
fact_Orders[Artikel Categorie] = "Contract",
fact_Orders[Order Marge Bedrag €] * 10,
fact_Orders[Order Marge Bedrag €]
)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.
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.
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.
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"
) * 10VAR RevenueOther =
CALCULATE(
SUM(Fact_Orders[OrderRevenue]),
Fact_Orders[OrderCategory] <> "Contract"
)RETURN
RevenueContract + RevenueOtherThis should work even if a column is causing issues in the table.
Ended up fixing my semantic model due to being granted access to it. This formula works now, thank you!
