Help with Average in Reference line

[#1]

Hello Guys,

I'm was wondering if someone could help me with an AVG in a reference line

I have a chart that uses this calculation to give me a percent for 12 months.. I want to show a reference line that shows the average from all the months.

=num (count (DISTINCT{$<soccer={"ball"}>}Incident_ID)
/
count (DISTINCT Incident_ID))

Hope I explained this right.

thanks,

Fred

[#2]

I been trying this, but it doesn't work??

avg(aggr(count(DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID)))

[swuehl · Accepted solution]

If you want to calculate the share in the context of all possible records, you can use the same expression as for the dimensional split:

=num (count (DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID))

But it seems you want the average of the monthly shares, something like

=Avg( Aggr( count (DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID), YourMonthField))

[swuehl]

You are missing dimension(s) for your advanced aggregation, the aggr() function. Something like the YourMonthField I used in my example (which you need to replace with your field from your data model.

[#5]

That works, thank you.

any chance you know how to make that into a percent in a text box?

[swuehl]

Both formulas should return the value also in a text box. Have you tried this?

You can enclose the formulas in a num() function to format as percentage:

=Num( Avg( Aggr( count (DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID), YourMonthField)), '#0.00%','.',',')

[#7]

perfect, thank you so much