Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Bar Chart - Average based on min values

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!

1 Solution

Accepted Solutions
MVP

=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.

9 Replies
MVP

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)

Not applicable
Author

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!

MVP

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.

Not applicable
Author

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

MVP

please share the sample data and the required output format

Anonymous
Not applicable
Author

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

Not applicable
Author

To much confidential data

Not applicable
Author

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

MVP