Groupby or Summarize by Two Columns from Different Tables

[HabibAdil]

Hi, I hope everyone is doing well. Can you please help me with the following:

I want to determine supplier monthly performance given the total orders value due for the month is more than certain amount (i.e. 10K). 

Supplier Performance = What % of PO Lines due in a given year-month were received in/on time, however only account for PO lines from the suppliers where the total amount due in the given month for the supplier was more than 10 k.

The supplier code is in PO Header table and the PO Details table contain the PO line value, due date. The GRN table contains when all the quantity for the PO Line was received and can be compared against due date in PO Detail table to deteremin the PO line was late or on time. The relationships between tables are shown as follow

PO Header (PO_No field) => PO_Detail (PO_No field)
Calender (Date Field) => PO_Detail (Due_Date Field)

PO_Detail (PO&Line Field)<=> GRN (PO&Line Field), The PO&Line Field is an aggregiated/calculated field.

I am struggling to use the Groupby or Summarize functions so I can determine if for a given month the total value for a given supplier is less than 10k hence filter out all the PO Lines belonging to that supplier. The otucome I want is a table as shown in the following picture

I treid the following, but in vain

https://community.powerbi.com/t5/Desktop/DAX-Grouping-on-multiple-columns-and-the-count/m-p/281118

https://www.sqlgene.com/2019/04/30/dax-error-the-expression-refers-to-multiple-columns-multiple-colu...

https://community.powerbi.com/t5/Desktop/group-by-two-columns-from-two-tables/m-p/813700 (This post says groupby cannot be used with columns from dfferent table and should use summarizecolumns which did not work either)

Regards, Habib

[HabibAdil]

@johnt75 Many thanks for your respone. Forgive my ignorence, but how does this account for the time period of each month. I want to display the results for each month. If I select a date of range of 01/01/2022 to 01/07/2022, for each month and supplier if total value due was not more than 10k, the PO lines for those suppliers should not be included in the calculation. Regards

[johnt75]

The code uses the relationship from calendar to the order detail table. By placing fields from your calendar table into the visual the filter will pass from the calendar table to order table, and so 

VALUES ( 'PO Detail'[PO number] )

will only return the rows which happened in the given time period. By using these values as a filter on the order header table only those orders will be considered

[HabibAdil]

@johnt75 Thanks for that. It works on visuals, but I need to use in another measure and calculated columns using the filter function with calculate.

[johnt75]

I think that you can get the suppliers more than 10k with

Suppliers more than 10k =
VAR PONumbers =
    VALUES ( 'PO Detail'[PO number] )
RETURN
    FILTER (
        VALUES ( 'PO Header'[supplier code] ),
        CALCULATE (
            SUM ( 'PO Header'[order value] ),
            TREATAS ( PONumbers, 'PO Header'[PO number] )
        ) > 10000
    )

You can then store this in a variable and use it as a filter in the % measure.