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 ne...
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.

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.