Forum Discussion
Filtering issue
Hi Rbakker888 -In such case,create a measure that explicitly calculates the filtered values for each table
Order Marge Bedrag Uren =
VAR OrderAmount =
CALCULATE(
SUM(fact_Orders[Order verkoop per periode]),
-- apply filter manually if needed, e.g. only for specific category or product
ALLSELECTED(fact_Orders) -- or use your dimensions here
)VAR LabourCost =
CALCULATE(
SUM(fact_Uren[ArbeidskostenTotaal]),
-- apply the same filter logic
ALLSELECTED(fact_Uren)
)RETURN
OrderAmount - LabourCostcheck the above and let us know.
This doesnt quite work for me, sorry for not mentioning it before but the total cost is based on the amount of hours an employee worked on it and the income is based on the order. There is no relationship between these tables and no relationship possible thats why I am doing it with this kinda ugly way which should work which it did but now it somehow broke only I cant figure out how.
The formula I use to calculate the revenue:
Hi Rbakker888 -In such case,create a measure that explicitly calculates the filtered values for each table
Order Marge Bedrag Uren =
VAR OrderAmount =
CALCULATE(
SUM(fact_Orders[Order verkoop per periode]),
-- apply filter manually if needed, e.g. only for specific category or product
ALLSELECTED(fact_Orders) -- or use your dimensions here
)
VAR LabourCost =
CALCULATE(
SUM(fact_Uren[ArbeidskostenTotaal]),
-- apply the same filter logic
ALLSELECTED(fact_Uren)
)
RETURN
OrderAmount - LabourCost
check the above and let us know.
This works just like my previous formula, it works fine until I add the filters. I found a little bit more info and that is that it works fine when I only filter on the order category but when I try adding the labour category's it suddenly sees the orders as 0 and just makes it all go negative.
Also the 2 columns you used in your formula are both measures but I assume that doesnt matter much?Nevermind sorry, this did work after some tweaking of the formula, thank you for your assistance!
My final formula:Order Marge Bedrag Uren Service =VAR OrderAmount =CALCULATE(fact_Orders[Order verkoop per periode],FILTER(fact_Orders, fact_Orders[OrderCategorie] = "Service"))VAR LabourCost =CALCULATE(fact_Uren[ArbeidskostenTotaal],FILTER(fact_Uren, fact_Uren[UICategorie] = "Service"))RETURNOrderAmount - LabourCost