Forum Discussion

GinjaNinja29's avatar
GinjaNinja29
New Member
6 years ago
Solved

How to sum column based on distinct values from another

Hello - these are samples of the relevant information in the table for which I am attempting to sum the quantity, based on the unique ID.

 

If the ID is found in the table more than once, I only need one line where the Quantity is summed.  Since I will be using this to calculate averages as well, it would be ideal if the result column simply displayed a blank (instead of 0) for the Result.

 

Thank You!

 

Original Table:

IDQuantity
11
12
14
21
37
33
25

 

 

Calculated Measure:

IDQuantity
17
26
310

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , GinjaNinja29 

    You may need to create calculated columns as below:

    Count 1 = CALCULATE(COUNT('Original Table'[ID]),ALLEXCEPT('Original Table','Original Table'[ID]))
    Quantity1 = IF('Original Table'[Count 1]>1,'Original Table'[Quantity],BLANK())

    The result will show as below:

     

     

    You also can try to create measure like this:

    Quantity 2 = 
var a= COUNT('Original Table'[ID])
return IF(a>1,CALCULATE(SUM('Original Table'[Quantity])),BLANK())

    In table visual , make sure the  fileld "id" show items with no data

     

    Here is a sample.

    pbix attached 

     

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    GinjaNinja29 ,

    For Avg you do not need to that, You can so like Avg of sum

     

    AverageX(summarize(Table,Table[ID],"_1",sum(Table[Quantity])),[_1])

    or
    AverageX(values(Table[ID]),sum(Table[Quantity]))

     

    • GinjaNinja29's avatar
      GinjaNinja29
      New Member
      6 years ago

      Thank you - I should be more clear.

       

      In one of my visuals, I will be also using this field to count is not blank. 

       

      This field will be used to count (is not blank), Sum, Average, and count if >3

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi , GinjaNinja29 

        You may need to create calculated columns as below:

        Count 1 = CALCULATE(COUNT('Original Table'[ID]),ALLEXCEPT('Original Table','Original Table'[ID]))
        Quantity1 = IF('Original Table'[Count 1]>1,'Original Table'[Quantity],BLANK())

        The result will show as below:

         

         

        You also can try to create measure like this:

        Quantity 2 = 
var a= COUNT('Original Table'[ID])
return IF(a>1,CALCULATE(SUM('Original Table'[Quantity])),BLANK())

        In table visual , make sure the  fileld "id" show items with no data

         

        Here is a sample.

        pbix attached 

         

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

  • jwessel's avatar
    jwessel
    Icon for Helper II rankHelper II
    4 years ago

    Based on the table supplied with ID and Quantity, what would be the way to just obtain the maximum quantity value for each ID in a vis ?  (PowerBI newby here) 😁