How to create Average DistinctCount?

[Anonymous]

Hi all,

I'm trying to create the following DAX:

CALCULATE

    (DIVIDE([Average Per Day],

        AVERAGE(DISTINCTCOUNT(AllInvoiceDetails[System]))),

            'AllInvoiceDetails'[Year] IN { 2021 }

    )

But when I type DistinctCount, it was not showing up.

Much appreciated for any help!

Note:  Average Per Day = $ 

[Anonymous]

@Anonymous , what is the value you are trying to caluclate? Are you trying to calculate the average invoice amount per day, across all systems, for 2021?

Some sample data would help. For example, could you please fill out what the Expected Result should be for the sample data I made up below, and what the [Average per Day] should return across all systems for 2021?  I have assumed that [Average per Day] within a system is simply the InvoiceTotal % number of days in the year.

System	Year	InvoiceTotal	InvoiceCount	Days	Average per Day	Expected result
System1	2021	1,001	3	365	2.74	?
System2	2021	2,003	13	365	154.08	?
System3	2021	3,007	51	365	8.24	?
	2021	6,011	67		?	?

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following formula.

Measure:

Measure =
VAR N1 =
    DISTINCTCOUNT ( AllInvoiceDetails[System] )
VAR N2 =
    AVERAGEX (
        FILTER ( ALL ( AllInvoiceDetails ), AllInvoiceDetails[Year] = 2021 ),
        N1
    )
RETURN
    DIVIDE ( [Average Per Day], N2 )

If that doesn't solve your problem, can you provide some sample data? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Pasted your measure into DAX Studio. Got a syntax error right off the bat. Please check out the exact syntax of the functions that you're trying to use.

[Anonymous]

In PBI, when we type the DistinctCount, it should show up on the list and then we can select, right? I can't even select, no list showing up. I manually type the above measure. 

[daXtreme]

If you can't see a function in the intellisense prompt, it means you cannot use it in the current context. Please, always check the syntax of the function you're trying to use. Some functions take columns, some tables, same can take arguments of different types (table, column, value). Here's a guide to all functions https://dax.guide/

This is my mere guess of what it should look like since I have no knowledge about your model:

[Your measure] =
CALCULATE(
  var AvgPerDay = [Average Per Day]
  var AvgNumOfUniqueSystemsPerDay =
    AVERAGEX(
      DISTINCT( DateDimension[Date] ),
      CALCULATE(
        DISTINCTCOUNT( 'AllInvoiceDetails'[System] )
      )
    )
  var Result = 
    DIVIDE( AvgPerDay, AvgNumOfUniqueSystemsPerDay )
  return
    Result,
    
  'DateDimension'[Year] IN { 2021 }
)

You do have a DateDimension, don't you? If you don't, then it's better to create it rather sooner than later. Please do not use a model that has one big table without any dims and facts. Such a model is notorious and will bite you sooner than you might think. Please get familiar with the techniques of building correct models: The importance of star schemas in Power BI - SQLBI (bing.com)