Exclude row filter in pivot but include filter from a column

Question

I was hoping somebody could help me with the below issue I'm experiencing.

I have a pivot based on the below table:

Supplier    qty new supplier new qty
supplier a  2   supplier a   2
supplier a  3   supplier b   3
supplier b  4   supplier b   4

My goal is to create a pivot with following fields:

• Supplier
• qty (this would be a simple measure: =sum(qty))
• new qty

In essence I would like to see: supplier a / 5 / 2. What would be the best approach to achieve this please? I believe this would be some combination of FILTER and ALL? Unfortunately, I cannot figure it out how to calculate it correctly. Any help would be much appreciated.

Thank you kindly.

Answer 1

The Qty measure should be like below :

Total Qty = SUM(Table[qty])

and the total New Qty :

 New Qty = 
VAR CurrentSupplier = MAX('Table'[Supplier]) 
RETURN
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[new supplier] = CurrentSupplier
    ),
    'Table'[new qty]
)

I created the CurrentSupplier variable to hold the current context supplier and to be able to use to filter the table to rows where new supplier matches this variable.