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

[Anonymous]

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

[AlexisOlson · Accepted 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.

[Anonymous]

Thanks a lot Alexis!!! 

[ValtteriN]

Hi,

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

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

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!

[Anonymous]

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,