cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to create Average DistinctCount?

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 = \$
1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Anonymous
Not applicable

@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 ? ?
Community Support

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.

Solution Sage

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
Not applicable

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.

Solution Sage

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)