Difference Distinctcount (formula) <-> Count (Distinct) within value

[posterme]

Hi,

where is the difference between the formula "DISTINCTCOUNT" and the "Count (Distinct)" within some visuals?

I do have a date/mont slicer, select 4 months. If I create a table with date and set the "Count (Distinct)" -> the value within the table is 4.

BUT, If a create a measure and want to divide the sales through the count of month (4), I do get another return as expected.

If I create a measure to check the value: MEASURE = DISTINCTCOUNT('Tablename'[Date].[Month]) I do get always 12 ...

Thanks for help.

[Greg_Deckler]

Very difficult to answer without data to test with. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

In general, DISTINCTCOUNT takes a column as an argument and returns the distinct number of values in that column. 

Now, DISTINCT, if used in a column context and not a table context will also return all of the unique values from the column. However, COUNT only counts Numbers, Dates and Strings so it is possible that it might not count something? However, it is also unclear from the documentation if DISTINCTCOUNT also only counts numbers, dates and strings or is doing something like a row count.

[posterme]

Hi, example:

count of Month = 2 and 12 :

Count of Month = 2Count of Month = 12

[Greg_Deckler]

OK, I see that, but I need sample data to recreate. Preferrably sample data that I don't have to manually type so something that I can copy and paste into an Enter Data query and then create calculations. I don't need the actual data, I just need a small portion of data that emulates your data.

[posterme]

Hey Greg,

thanks. I will try to export some data an recreate my scenario.

But maybe I can explain it a little bit easier or maybe I think to difficult: I need the Count of the selected month from the slicer to divide sales with the count of month, e.g. 1.000.000 $ Sales in 3 month (selected are FEB,MAR & APR) = 333.333,33 $ per month (so I have an average from this 3 months)...

Thanks!

[Greg_Deckler]

I would have never figured that out from your first two posts. In that case, the answer is quite simple:

Number of Months = COUNTROWS(DISTINCT(Calender[Month]))

A little explanation goes a long way.

[posterme · Accepted solution]

Hi,

I have a "workaround":

Measure 1: Month_Name = 'Sales'[Date].[Month]

Measure 2: Month_Count = DISTINCTCOUNT('Sales'[Month_Name])

And then I can create a Measure: Average = 'Sales'[Turnover] / [Month_count]