Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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's avatar
daXtreme
Solution Sage
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    daXtreme
    Solution Sage
    4 years ago

    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)