Sum a distinct count of a group

[Anonymous]

I want to sum a distinct count of a group so I can use the sum in other calculations.

• For example, I have a group of building materials called "paint."
• Paint has a subgroup of "paint types." These each have unique SKUs that give a count.
• How do I get the count of building materials in the example below and display it in every row? These add up to the sum of the "Paint" Building Material.

Building Material	Subgroup	Count Building Material	Count SKUs
Paint	Blue	501	29
Paint	Red	501	99
Paint	Yellow	501	373
Fasteners	Nails	236	56
...

[parry2k]

@Anonymous try this measure

Count = 
CALCULATE ( SUM ( Table[Material Count Skus], ALLEXCEPT ( Table, Table[Building] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Building Material	Subgroup	Count Building Material	Count SKUs
Paint	Blue	4	1ZEDRFDS
Paint	Red	4	34SDFDS
Paint	Yellow	4	DGDFSGH3
Paint	Yellow	4	EGDFDGS3
Fasteners	Nails	1	SDFWEW2
...

Thanks, parry. I actually need a distinctcount of the skus, as I said in the title. Sorry, if the example I gave was bad. Here is hopefully a better example of what I am after.

I want to show the total distinctcount for all of the SKUs for the  Paint group, at the row level. How would I achieve that?

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use SUMX and DISTINCTCOUNT function to meet your requirement.

Measure Count Building Material =
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Building Material] = MAX ( 'Table'[Building Material] )
    ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Count SKUs] ) )
)

If it doesn't meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[sikan40]

Hi @v-zhenbw-msft 
Thank you for your solution.

I got the same requirement to count the sum of distinct instance ids for a group of dates. When i used ALLSELECTED('TableName') it didn't work. instead of taking the whole table i took the respective columns inside ALLSELECTED and it worked.
Instance Count=

SUMX (
    FILTER (
        ALLSELECTED ( 'Table[respective Col1]','Table[Respective Col2]' ),
        'Table'[Either Respective Col1 or Respective Col2] = MAX ( 'Table'[Either Respective Col1 or Respective Col2] )
    ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Instance IDs] ) )
)