Average of a calculated field

[Anonymous]

Hello Guys,

I have this table. the numbers are the result of the distinct count founction that I applied on a specific field. Now I need the average of each raw. I 've tried to apply the AVERAGE('TABLE'[CALCULATED FIELD]) but Power BI say the field doesn't exist and underlines that with red line. Please can someone help me?

Thanks,

Ulrich

[Anonymous]

Any help please?

[Nathaniel_C]

Hi @Anonymous , not sure how you were trying to do this, but the first pic shows it being done as a table.  The second as a visualization and measure.  The second pic shows the original table that I built, then using the Distinct Count Measure and finally the measure which also uses SummarizeColumns(). I have included the code below.

EID Distinct Count = DISTINCTCOUNT('Table'[ID])

======================================

Average EID by Date = 

var _summarize = SUMMARIZECOLUMNS('Table'[Date],"test1",[EID Distinct Count])

var _calc = AVERAGEX(_summarize,[test1])

return _calc
=====================This is what creates the standalone 4.

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Will log back later today.
Nathaniel

[Nathaniel_C · Accepted solution]

Hello, @Anonymous , not sure what you are looking to do here, but perhaps you could supply the  measure you used, and the expected outcome.  The measure that you wrote with the distinctcount is measure, not a field, and then it looks like you filtered it with the visual using days. So you may have to do something like Calculate([distinctcountmeasure],filtered) or use averagex on your original table.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

Hello Nathaniel_C

thanks for your reply. yes, you're right. I created a new measure by applying a distinctcount on a field. let say I have a field named "USER ID". Now I did a distinctcount on that field and I got the value for each week. since I am using a weekending filter (let's say I display the values just for the last 4 weeks), I need the average of that distinctcount measure for those last 4 weeks. 

Thanks,

Ulrich