Count of the number of instances making up a sum

[Anonymous]

I am trying to create a column that shows how many items make up a sum.  In Excel I might use a PivotTable on a PivotTable to achieve this:

So the 3rd table shows that the metric for two UIDs sums to 1, one UID to 2 and two UIDs to 3

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a calculated column

sum = CALCULATE(COUNT('Table'[uid]),ALLEXCEPT('Table','Table'[uid]))

Create a measure

uid count = CALCULATE(DISTINCTCOUNT('Table'[uid]),ALLEXCEPT('Table','Table'[sum]))

Best Regards

Maggie

Community Support Team _ Maggie Li

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

[JosefPrakljacic]

Hey @Anonymous 

Does this help? 

If not would you mind to provide us with copyable sample data or a sample file?

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

[Anonymous]

Hi Josef, 

It doesn't, in your example the UID counts should equal 1 for all because each Metric value has one UID associated with it.  Unfortunately I can't use file sharing services, but this is how the new table or column would look:

The equivalent SQL syntax would be:

WITH A AS (
  SELECT 
    ID,
    SUM(METRIC) 'SUM'
  FROM MERGED_DF2
  GROUP BY ID
)
SELECT
 SUM,
 COUNT(*) 'UIDS WITH SUM'
FROM A
GROUP BY SUM