Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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
v-zhangti
Community Support
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.

View solution in original post

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.

 

SystemYearInvoiceTotal InvoiceCount Days Average per Day Expected result
System1 20211,00133652.74?
System2 20212,00313365154.08?
System3 20213,007513658.24?
 20216,01167 ??
v-zhangti
Community Support
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.

daXtreme
Solution Sage
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. 

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)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.