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.

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.