Forum Discussion

Rbakker888's avatar
Rbakker888
Helper II
1 year ago
Solved

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 work...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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's avatar
Rbakker888
Helper II
1 year ago

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's avatar
rajendraongole1
Super User
1 year ago

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.