cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Assistance applying a distinct clause to a calculation

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
1 ACCEPTED SOLUTION
Super User

@Dayna , Try a measure like

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

4 REPLIES 4
Super User

@Dayna , Try a measure like

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

Helper V

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
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.