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.
Hi Rbakker888 - If youare filtering for 2 specific categories (e.g. "Category A" and "Category B"), and you want the revenue only for those,
create a measure as below:Eg
RevenueFiltered =
CALCULATE(
[Total Income],
'CategoryTable'[Category] IN { "Category A", "Category B" }
)
-
CALCULATE(
[Total Costs],
'CategoryTable'[Category] IN { "Category A", "Category B" }
)
This way, the filter is explicitly applied, and consistent for both parts of the revenue measure.Make sure the relationships between your category, cost, and income tables haven’t been accidentally set to inactive or changed.
Hope the above logic helps.
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:Order Marge Bedrag Uren = fact_Orders[Order verkoop per periode] - fact_Uren[ArbeidskostenTotaal]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 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?
