Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

How to count distinct by a category but add up the distinct values

Hi,

 

I have been struggling to add the distinct count of column [Master Brand], grouped by [Operating Unit], [Level] and [L0 Masterbrand OU Sales vs actual date]. The example table is below

In other words, I can get the distinct count by [operating unit], but the total is also a distinct count, therefore it is 1 Fig. 1. What I want is to get the sum of each of the [Operating Units] - Fig 2

 

Thanks a lot for your help!!

 

Fig. 1

ebricvelasco_0-1639638267399.png

Fig 2

ebricvelasco_1-1639638545870.png

 

ebricvelasco_0-1639638912105.png

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You can iterate and sum over whatever granularity you choose. For example, at the granularity you specified:

SUMX (
    SUMMARIZE (
        Table1,
        Table1[Operating Unit],
        Table1[Level],
        Table1[L0 Masterbrand OU Sales vs actual date]
    ),
    CALCULATE ( DISTINCTCOUNT ( Table1[Master Brand] ) )
)

 

I'm not sure how meaningful this is though since it will be counting the same brand multiple times.

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

You can iterate and sum over whatever granularity you choose. For example, at the granularity you specified:

SUMX (
    SUMMARIZE (
        Table1,
        Table1[Operating Unit],
        Table1[Level],
        Table1[L0 Masterbrand OU Sales vs actual date]
    ),
    CALCULATE ( DISTINCTCOUNT ( Table1[Master Brand] ) )
)

 

I'm not sure how meaningful this is though since it will be counting the same brand multiple times.

Anonymous
Not applicable

Thanks a lot Alexis!!! 

ValtteriN
Super User
Super User

Hi,

How are you calculating DISTINCTCOUNT?
In this example you can get the total of distinctcount:

DistinctCount = DISTINCTCOUNT('Matrix example'[Company])

ValtteriN_0-1639638876054.png

I hope this helps to solve your issue and if it does consider accepting this post as a solution and giving it a thumbs up!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi, 

Thanks for your help, but the total result I am getting is still 1. In the example below, I have the brand the brand ZZZ in all the Operating Units.

 

The Measure from Alexis solved my problem. 

 

Thanks and regards, 

 

ebricvelasco_0-1639710052988.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.