Forum Discussion
How to create Average DistinctCount?
Hi all,
I'm trying to create the following DAX:
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
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.
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)
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.
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 ? ?
