Need help with measure for combining data from 2 tables

[RichOB]

Hi, I'm looking for help with a measure please.

I need the measure that grabs the amounts from only Cleaning, Training, Utilities...and the filtering part of the measure to use the Category_name, not the GL_code. So in a Card it would display Combined_Amount 330.

Table 1: Cat_Codes

GL_code	Category_name
GL1	Cleaning
GL2	Cleaning
GL3	Cleaning
GL4	Utilities
GL5	Utilities
GL6	Utilities
GL7	Utilities
GL8	Training
GL9	Training
GL10	Salaries

Table 2: Amount_Table

GL_code	Balance
GL1	£45
GL2	£45
GL3	£50
GL4	£25
GL5	£20
GL6	£15
GL7	£35
GL8	£45
GL9	£50
GL10	£200

Combined_Amount =

CALCULATE(

    SUM(Amount_Table[Balance]),

    Cat_codes [Category_name] IN {Utilities, Training, Cleaning}

)

I need the measures to be somewhat like the above (but correct) where the amounts are drawn from the balance and the categories are drawn from what category names are input, please. 

For more context, I need the category (not GL_code) in the measure, as in my real-life scenario there are thousands of GL_codes and only a handful of Category_names which makes it more realistic to create. Also, I need this as a measure not to just unselect Salaries from the Filter pane.

Thanks for your help

[AnkitKukreja]

Hi! @RichOB 

As suggested by @v-cgao-msftthis can be achieved with or without the relationship between the tables. In case your model has these 2 tables only then best practice would be to have a relationship between the tables and your measure should also work or you can add filter to the page while simply using sum measure.

[v-cgao-msft · Accepted solution]

Hi @RichOB ,

If the filter from the ‘Cat_Codes’ table can be passed to the ‘Amount_Table’ table, then there seems to be no problem with your code, you just need to add double quotes to the text values in it:

IN {"Utilities", "Training", "Cleaning"}

Or you want that part of the result to be dynamic:

Combined_Amount = 
CALCULATE(
    SUM(Amount_Table[Balance])
)

Either there is no relationship between the two tables, or the filter cannot be passed.

Fixed:

Combined_Amount = 
CALCULATE(
    SUM(Amount_Table[Balance]),
    TREATAS(CALCULATETABLE(VALUES('Cat_Codes'[GL_code]),'Cat_Codes'[Category_name] IN {"Utilities", "Training", "Cleaning"}),'Amount_Table'[GL_code])
)

  Dynamic:

Combined_Amount = 
CALCULATE(
    SUM(Amount_Table[Balance]),
    TREATAS(VALUES('Cat_Codes'[GL_code]),'Amount_Table'[GL_code])
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[bhanu_gautam]

@RichOB , Try this

Combined_Amount =
CALCULATE(
SUM(Amount_Table[Balance]),
FILTER(
Cat_Codes,
Cat_Codes[Category_name] IN {"Cleaning", "Training", "Utilities"}
)
)