Filtering issue

[Rbakker888]

Hi so I have an issue with my dashboard, the issue is as follows:
On the left picture you can see at the bottom 2 cards, the left one displays costs and the left one displays how much money came in from those costs so if we would calculate the difference we have the revenue. At the top you can also see 2 cards, the one on the right shows the total revenue without any filters which is correct cause you dont see it but the 2 cards at the top also have filters cause I only want to see the revenue for 2 category's. When I apply those filters tho, it doesnt look at the total money coming in and just sees it as 0 for some reason. The filters I use you can see on the left. 

This is weird for ofcourse the obvious reason that it shows fine at the 2 cards below and just for your information the formula to calculate the difference is just the bottom 2 cards values minus each other. The other thing is that it was fully working a few weeks ago.

I hope someone can help me here, if you need any other info please let me know.

[rajendraongole1]

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.

[Rbakker888]

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]

[rajendraongole1 · Accepted solution]

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.

[Rbakker888]

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?

[Rbakker888]

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")

)

RETURN

OrderAmount - LabourCost