Help with FILTER measure with data from other tables

[Syndicate_Admin]

Good to all,

I need your help to make a calculation of a table. This is a sum of the value of one table that meets the filter requirements of another related table.

I upload the .pbix and I put the filters to make to get to the calculation.

In the "AATT_Presupuestación and Planning" table, I need to filter by:

• Last date according to the "DataKey" field.
• "Version # "=CI
• "Type AATT"=Technical Assistance
• "Actual end" = current year dates (DATESYTD)

That will give me a sample and I will make a summary of the cmpo "Últ.Nº Horas DID". So this first part I already have with this measure:

CALCULATE(SUM('AATT_Presupuestación and Planning'[Last Did'), 'AATT_Presupuestación and Planning'[Version No]="CI",'AATT_Presupuestación and Planning'[DataKey]=_lastdate,YEAR('AATT_Presupuestación and Planning'[Actual End])=YEAR(TODAY()))

That would be my denominator.

But the numerator is in another table: "service AATT_Acuerdo". And the field I want to add from this table as a numerator is "Total Cost (closure)".

But of course! I don't want you to add up all the "Total Cost (closing)" records. I want you to add me the records of the Technical Assistances that are in this table and meet the filter requirements mentioned above.

I don't know how to do it. I tried FILTER, USERELATIONSHIP... And it doesn't give me the right result.

The final calculation would be "Total Cost (closing)" (with all filters in the table "AATT_Presupuestación and Planning")/"Last Hours DID" (with all its filters.

Attachment .pbix.

https://1drv.ms/u/s!AggHtWEAB2tKgU4V-Bq_ywCZGbMY?e=O8JG6G

[v-xiaotang · Accepted solution]

Hi @Syndicate_Admin 

Does your denominator measure work OK? If yes, we just need to create numerator measure, right?

-

Answer:

According to the relationship between the 2 tables,

numerator Measure =

CALCULATE(SUM('AATT_Acuerdo de Servicio'[Total Coste (cierre)]),FILTER(ALL('AATT_Acuerdo de Servicio'),'AATT_Acuerdo de Servicio'[AATT/Proyecto]="Asistencia Técnica"),FILTER(ALL('AATT_Presupuestación y Planificación'),'AATT_Presupuestación y Planificación'[Nº de versión]="CI"&& your other filter condition1 && your other filter condition2))

Besides, one quick example,

Measure = CALCULATE(SUM('Table1'[sales]),FILTER(ALL('Table1'),'Table1'[filter1]="CI"),FILTER(ALL('Table2'),'Table2'[filter2]="BB"&&'Table2'[filter3]="F"))

See sample file attached bellow.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.