Forum Discussion

Dayna's avatar
Dayna
Helper V
4 years ago
Solved

Sub total issue with sum containing duplicate values

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

3 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    4 years ago

    Dayna You need to create a measure like below:

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

     

    • Dayna's avatar
      Dayna
      Helper V
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.