Sub total issue with sum containing duplicate values

[Dayna]

Hello,

I have a simple value which is Quantity, I have added this to a table and using the standard PowerBI function it is doing a sum - this is just a field, not a measure and no calculation applied.

This quantity field is is quantity for a component. My table shows products which may have the same component, i.e.

Product     Component     Quantity

ProductX   Comp1             10 

ProductY   Comp2             15

ProductZ   Comp1             10

For the above, the quantity sub total should be 25, as it's the sum of the quantity values for each unique component, but currently it is bring back 35 as it's summing every row.

I suspect rather than adding the quantity field, I need to wrap it in a calculation, but what do I need to do to make the sub total then only sum the distinct component values on the table?

Many thanks,

Dayna

[Anonymous · Accepted solution]

Hi @Dayna ,

I believe you are referring to the totals circled in the screenshot below.

Here's the solution. You can create a measure.

Measure  = VAR _t=DISTINCT(SELECTCOLUMNS('Table',"C",[Component],"Q",[Quantity]))
RETURN SUMX(_t,[Q])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tahreem24]

@Dayna You need to create a measure like below:

MEASURE = CALCULATE(SUM(Table[Quantity]),ALLEXCEPT(Table,Table[Component]))

[Dayna]

Hello,

I did that but the numbers were exactly the same as without the measure, but perhaps I've not used the right component in the latter part - I used the one on the table with the value, rather than the one used in the table. If I did that, then it showed a lot of components that it should do in the grid!

Can you assist?

Thanks,

Dayna