Forum Discussion

android1's avatar
android1
Icon for Post Patron rankPost Patron
8 years ago

Subtract 2 measures from different tables

Hi,

 

How do I subtract these 2 measures from different tables?

 

I have a simple measure Carers who worked = CALCULATE(DISTINCTCOUNT(vw_PivotWagesHrs[CarerRef])) which tells me how many people who worked. Filtered over Weeknum.

 

This measure below (in table Carerslist) shows me the total available workforce in any particular week.

 

 YTD Workforce running total in Weeknum =
CALCULATE(
[YTD Workforce],
FILTER(
ALLSELECTED('MyDateTable'[Weeknum]),
ISONORAFTER('MyDateTable'[Weeknum], Max('MyDateTable'[Weeknum])-1, DESC)
)
)

 

I want to figure out how many people did not work in each week ie  YTD Workforce running total in Weeknum - Carers who worked.

 

The relationship between the 2 tables(CarersList & vw_PivotWagesHrs) is many to one Single cross filter.

 

Relationship between CarersList & MyDateTable is many to one Single cross filter.

 

 

 

Need Help

1 Reply

  • Veles's avatar
    Veles
    Icon for Advocate V rankAdvocate V
    8 years ago

    So long as the fields you are using as filter context (i.e. what you're using for rows on a table visual) affects both tables you can literally just make a measure as you've written it.

     

    You might need to switch some relationships to be bi-directional, or create a new dimension tables (e.g. a date table that has a relationship to both of your tables).