Forum Discussion
Multiplying based on category
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"
) * 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.
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.