Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
Fig 2
Solved! Go to Solution.
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.
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.
Thanks a lot Alexis!!!
Hi,
How are you calculating DISTINCTCOUNT?
In this example you can get the total of distinctcount:
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!
Proud to be a Super User!
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,
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |