Matrix Table with Sales & Purchase by Supplier

[eddiechang]

Hi,

I looking for a solution to have a matrix table that shows:

After i select a supplier by slicer

1. Purchase measurement: How much i purchased from this supplier by product

2. Sales measurement: For those only product i purchased from this supplier, how much it sold regardless of customer

in data model:

bi_bill[DocType1] = "AR" is Sales

bi_bill[DocType1] = "AP" is Purchase

Enclosed with the pbix. Really wish any experts can clear my doubt. This stucked me for weeks.

Thanks.

pbix

[v-ljerr-msft]

Hi @eddiechang,

Could you try the formula below to see if it works in your scenario? 

Sales =
VAR currentProductName =
    FIRSTNONBLANK ( bi_product_master[ProductName], 1 )
RETURN
    CALCULATE (
        SUM ( bi_bill[FinalAmount] ),
        FILTER (
            ALLEXCEPT ( bi_bill, bi_bill[StoreNo], bi_bill[DocDate] ),
            RELATED ( bi_product_master[ProductName] ) = currentProductName
                && bi_bill[DocType1] = "AR"
        )
    )

Regards

[eddiechang]

Hi @v-ljerr-msft,

Thanks for the reply. Unfortutenately it not work.

[SalesAll] is the measurement i used as you provided.