How to limit one measure based on the other measure for total numbers

[SLucky]

Hi,

I would like to limit the total number of "2022 Labor Expense" based on non-blank values in "2019 Labor Expense". 

For Example we have data as below. Left hand side is the original view with company breakdown. When I add the filter for non-blank of "2019 Labor Expense", the total of "2022 Labor Expense" changes to -$18,783 which is the number I want. 

My question is if I remove "code" variable (it is company name) to show the total, and add filter for "2019 Labor Expense" to the view, the total of "2022 Labor Expense" stays the same as -$35,565. How can I make it to -$18,783? Thanks!!

[Datagulf]

is it good now @SLucky 

[SLucky]

Yes, thanks!

[tamerj1 · Accepted solution]

Hi @SLucky 

please try

2022 Labor Expense Filterd =
VAR Codes2019 =
    SELECTCOLUMNS (
        CALCULATETABLE ( 'Labor Data', DATEADD ( 'Calendar Dates'[Date], -3, YEAR ) ),
        "@Code", 'Labor Data'[Code]
    )
RETURN
    CALCULATE (
        [2022 Labor Expense],
        FILTER ( 'Company Code', 'Company Code'[Code] IN Codes2019 )
    )

[SLucky]

Thank you so much @tamerj1 

It works!

[SLucky]

Hi @Datagulf 

Move conversation here. I would like to see the total of 2022 labor expense but filtered with individual company that has value in 2019 labor expense. The final report only has one line. Format sample see below(2022 labor expense should be -$18,783). Thank you!

[SLucky]

Hi @Pragati11

Here is the problem. I would like to see the total of 2022 labor expense but filtered with individual company that has value in 2019 labor expense. The final report only has one line. Format sample see below(2022 labor expense should be -$18,783). Thank you!

[tamerj1]

@SLucky 

This is what the measure was intended to do. However I discovered an error in the code, I will edit now but what results did you get using this code?

[SLucky]

Hi @tamerj1 

Thanks for your quick response! I appreciated it. I haven't use the code. 

[tamerj1]

@SLucky 

Ok then try this one

2022 Labor Expense Filterd =
VAR Codes2019 =
    SELECTCOLUMNS (
        CALCULATETABLE ( TableName, 'Date'[Year] = 2019 ),
        "@Code", TableName[Code]
    )
RETURN
    CALCULATE (
        [2022 Labor Expense],
        FILTER ( TableName, TableName[Code] IN Codes2019 )
    )

[SLucky]

Hi @tamerj1 

I checked the company code. Most of the companies have value in some days of 2019 while in some day they don't. If I would like to see data for all days in 2022, I cannot use the fixed list of company code.

[tamerj1]

@SLucky 

Can you explain step by step how we're supposed to reach to this number ( -$18,783 )

[SLucky]

Hi @tamerj1 

Sorry I am not saying clear.

I have three tables: a data table, a company code table and a labor expense table.  

2022 labor expense: it is the sum of the labor expense from labor expense table.

2019 labor expense: it is a dataadd measure from 2022 labor expense variable. 

I would like to see the total of 2022 labor expense in any date but only calculate the company where it has value in the same day in 2019.

Let's say we have 10 companies in total. On 1/1/2019, 5 companies have value in 2019 labor expense variable. Then the total of 1/1/2022 labor expense will be the sum expense of those 5 companies. On 5/1/2019, if only 2 companies have value in 2019 labor expense, the total of 5/1/2022 labor expense will sum labor expense of those 2 companies.

[tamerj1]

@SLucky 

Great. Can you please

1. Share a screenshot of your data model with the relationships?

2. Share the code of both 2019 and 2022 measures?

[SLucky]

@tamerj1 

1. Table relationship: calendar table and property table don't have any relationship. Labor expense table has 1:many for calendar table(Date) and property table(Code).

2. 2022 Labor expense = SUM('Labor Data'[expense])

2019 Labor expense = calculate([2022 Labor expense], dateadd( 

'Calendar Dates'[Date], -3, year))

[tamerj1]

@SLucky 

Sorry for asking too many questions but how do you filter for the year 2022? Are you using a slicer?

[SLucky]

Hi @tamerj1 

I just found that the relationship I mentioned above is wrong. Sorry. 

Labor expense table has many:1 for calendar table(Date) and property table(Code)

[SLucky]

Hi @tamerj1 

Yes, I am using a slicer.

[tamerj1]

Hi @SLucky 

is the date from the same table or you have a date table?

[SLucky]

Date and Code are columns from two different tables. The other two are measures

[tamerj1]

@SLucky 

Please try

2022 Labor Expense Filterd =
VAR Codes2019 =
    SELECTCOLUMNS (
        FILTER ( TableName, RELATED ( 'Date'[Year] ) = 2019 ),
        "@Code", TableName[Code]
    )
RETURN
    CALCULATE (
        [2022 Labor Expense],
        FILTER ( TableName, TableName[Code] IN Codes2019 )
    )