Bar Chart - Average based on min values

[#1]

Hi experts,

I've the following data model:

    Dim_Team:
          -ID_Team
          - Coach

    Dim_Player:
          - ID_Player
          - Name
          - Position
     Dim_Time:
          - ID_Time (0...90)
          - Game_Shift (1_Half and 2_Half)
     Dim_Calendar:
          - Date_ID
          -Year
          -Month
          -Day
   Fact_Game:
         - ID_Game
          - ID_Player
          - ID_Time
          - ID_Team
          -ID_Date
          - Num_Goals

My goal: On average , how many first goals occur by ID_Time

What I've is:

IF(ID_Time = Min(ID_Time), Count(Num_Goals))

But I isn't correct because it doens't give me the average. I'm trying to use this on bar chart.

Thanks!
         

[swuehl]

Maybe like this:

Create a histogram for first goals:

1) Create a calculated dimension like

=Aggr( Min(ID_Time), ID_Game)

2) Then create an expression like

=Count(Distinct ID_Game)

or if you want to get the percentage:

=Count(Distinct ID_Game) / Count(TOTAL DISTINCT ID_Game)

[#3]

Thanks for your answer

With that I get the total by ID_Time, right? How can get the average?

Maybe create a variable with the count then in expression use:

AVG(variable).  ?

Thanks!

[tresesco]

Not sure if I understood right. May be, like:

Avg({<ID_Time = {"$(=Min(ID_Time))"}>} Num_Goals)

if not, try to share a representative sample.

[#5]

The question that I want to answer is (imagine):

At the Time "20" (ID_Time), in average, how many times occur the first goal of the match

[avinashelite]

please share the sample data and the required output format

[Anonymous]

Avg({<ID_Time = {"$(=Min(ID_Time))"}>} Num_Goals)

[#8]

To much confidential data  

[#9]

Imagine that the chart have the hour in dimension:

1: In average how many 1ªfirst goals occur in the ID_Time = 1
2: In average how many 1ªfirst goals occur in the ID_Time = 2
3: In average how many 1ªfirst goals occur in the ID_Time = 3
4: In average how many 1ªfirst goals occur in the ID_Time = 4
5 In average how many 1ªfirst goals occur in the ID_Time = 5

To know when is a 1ªfirst goal, I have a timestamp column to identify the minimum...

I think your expression is correct -

Avg({<ID_Time = {"$(=Min(ID_Time))"}>} Num_Goals)

However, with that the chart only return the first time value

[swuehl · Accepted solution]

What about the percentage I suggested in my previous answer:

=Count(Distinct ID_Game) / Count(TOTAL DISTINCT ID_Game)

This will normalize the absolute numbers by dividing by the number of games. So you can compare e.g. different seasons or teams better.

Or maybe I am missing what you are after. Could you post a small sample distiribution and what you expect as 'average'?

You can use mock-up data, so no need to post confidential data. Just data to clarify your setting and where you're heading to.