How to use dax expression, functions or formula to find the sum of a DISTINCTCOUNT (rows w/ text)?

[lm_i]

Hi,

I am pretty new to Power BI so I will use my work to explain.

I found the distinct count of customers who purchased specific products. Measure: No. Sold In = DISTINCTCOUNT(Table Name[CustomerNames]).

I now need to find the *Total No. of Customers who made purchases. I assumed SUM(DISTINCTCOUNT(Table Name[CustomerNames])) would work but of course it didn't among several other options.

See pic below (actual products blocked out).

Coverage % = No. Sold In (mentioned above) / *Total No. Distinct Customers (circled in red in pic and mentioned above)

I used the actual no. of distinct customers in Coverage % calculation (seen in pic) to show why the sum of the distinct count is necessary.

A generalized DAX expression or formula is necessary as the aactual value will vary from month to month and these values will also be filtered per month.

Any assistance will be appreciated!

[jdbuchanan71]

@lm_i 

You can use your customer count measure and apply a filter to only count customers that are in your sales table.  It will be something like this.

Customers with Sales = CALCULATE ( [No. Sold In], YourSalesTable )

[lm_i]

Hi @jdbuchanan71,

Firstly, thank you for your response!

Your measure yields the correct total however, when done on a row by row level for each product in the visual,

No. Sold In = Customers with Sales hence eg. Coverage = 74/74 = 1 = 100%

[jdbuchanan71]

@lm_i 

Sorry, I misunderstood.  I thought you were wanting to go the other way.  You can write your measure to calculate the total customers for all sales over the product specific like so.

Coverage % = 
VAR _All = CALCULATE( [No. Sold In], ALL ( Sales ) )
RETURN 
    DIVIDE( [No. Sold In], _All )

[lm_i]

Hi @jdbuchanan71 ,

This measure works!

 Thank you for taking the time to recreate the table and insert visuals.

Your assistance is very much appreciated!!!

[v-kelly-msft]

Hi @lm_i ，

Glad to hear that your issue has been solved,could you pls mark the reply as answered to close it?

Much appreciated.

 
Best Regards,
Kelly

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

[lm_i]

Hi @v-kelly-msft ,

I have been trying to mark as the solution, however it keeps saying authentication has failed.

[v-kelly-msft]

Hi @lm_i ,

Refresh your page then enter your user name and password.🙂

Best Regards,
Kelly

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

[lm_i]

Hi @v-kelly-msft,

I am still not able to mark the solution. I received the same error message several times.