Assistance applying a distinct clause to a calculation

[Dayna]

Hello,

I have a calculation which sums the field 'tr_qty_loc' based on certain scenarios, but, due to how this table is joined, there's some duplicate rows that I need to ignore.

In effect, I need to sum the values as per this expression, but for distinct rows by the field 'tr_trnbr'.

Here's the expression:

ISS-WO Qty (Incl. Waste) =

VAR ResultOne =

SUMMARIZE(VALUES(Waste),

Waste[tr_trnbr],

"DSTN ISS", CALCULATE(SUM(Waste[tr_qty_loc]), Waste[EXPR4] = "01")

)

RETURN

SUMX(ResultOne, [DSTN ISS]) + 0

In a QlikView world, it was done by this:

sum(aggr(sum({$<EXPR4 = {'01'}>} distinct tr_qty_loc),tr_trnbr))

My issue at the moment is the fact that currently PowerBI is duplicating this value as there's two rows with the same ID for tr_trnbr, but using aggr / distinct in Qlik, I was able to filter this out.

Can anyone assist, please?

Note, the expression in PowerBI was not done by myself, but by a contractor who is no longer with us. 

Many thanks,

Dayna

[amitchandak · Accepted solution]

@Dayna , Try a measure like

Sumx(summarize(Waste, Waste[tr_trnbr],Waste[tr_qty_loc]), [tr_qty_loc])

[Dayna]

Hello,

How would that work with the existing expression, as this is also filtering where EXPR = 1?

ISS-WO Qty (Incl. Waste) =

VAR ResultOne =

SUMMARIZE(VALUES(Waste),

Waste[tr_trnbr],

"DSTN ISS", CALCULATE(SUM(Waste[tr_qty_loc]), Waste[EXPR4] = "01")

)

RETURN

SUMX(ResultOne, [DSTN ISS]) + 0

[Anonymous]

Hi  @Dayna,

You can update the formula of meaure [ISS-WO Qty (Incl. Waste) ] as below, the part with red font is updated one...

ISS-WO Qty (Incl. Waste) =
VAR ResultOne =
    SUMMARIZE (
        VALUES ( Waste ),
        Waste[tr_trnbr],
        "DSTN ISS", CALCULATE ( SUM ( Waste[tr_qty_loc] ), Waste[EXPR4]  IN { "1", "01" } )
    )
RETURN
    SUMX ( ResultOne, [DSTN ISS] ) + 0

If the above one can't help you get the desired result, please provide some sample data in the table Waste (exclude sensitive data) and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[Dayna]

Hello,

Thanks for the response, unfortunately that expression didn't include the requirement of only showing distinct values for the tr_trnbr, using the expression from @amitchandak with a slight tweak gave me the result I needed. It looked like this in the end:

calculate(Sumx(summarize(Waste, Waste[tr_trnbr],Waste[tr_qty_loc]), [tr_qty_loc]), Waste[EXPR4] = "01") 

Thank you all for your help!