Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Dayna
Helper V
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
amitchandak
Super User
Super User

@Dayna , Try a measure like

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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Dayna , Try a measure like

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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
Not applicable

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

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!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors