Forum Discussion
How to create Average DistinctCount?
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 | ? | ? |