Forum Discussion
Difference Distinctcount (formula) <-> Count (Distinct) within value
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]
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.
Hi, example:
count of Month = 2 and 12 :
Count of Month = 2Count of Month = 12
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.
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!
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]