average of sum in a table

[shaashn]

HI all,

I have a matrix of values containing weekly total of head count. The matrix is aggregating the total HC from from a table where the data is at granular level.. that is ..each week has data for X no of regions, Y no of Towers  so on.  What I need is the sum of HC by week on each row,,and average of totals below. I need average (sum of Week 1+Week 2+Week3..so on)/no of weeks disaplyed in table. However, it is showing the sum of the totals and not avarage. when i select average under field aggregation the average shows "1" probably because it is averaging all rows at a granular level and not averaging the sum of each week. Would appreciate you help.

[v-stephen-msft]

Hi @shaashn ,

Without your data model, what I can think of is the following metrics, please try

Average =
DIVIDE (
    CALCULATE ( SUM ( 'Table'[HC] ), ALLSELECTED ( 'Table' ) ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[week] ), ALLSELECTED ( 'Table' ) )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

You should be using measures instead of implicit aggregations. (best practice!)

So... create a measure for the sum:

Sum HC = SUM (Table [HC])

Then another for the average

Average = AVERAGEX (table, [Sum HC])

and finally for the visual:

Final = IF(ISINSCOPE (Table[ Week]), [Sum HC], [Average])

If unproductive, please provide sample data or PBIX file

[speedramps]

Ho shaashn
Try this and please leave kudos.

Create a calendar table with contiguous dates or weeks (contiguou means with no missing gaps).
There are lots of Youtube videos that will show you how to create a list of weeks or dates.

Create a 2 relationship between the calendat table and your X and Y tables.

Then use the calenadt week instead of the X / Y dates in your reports.

This will force 1 row per week as required. 

[shaashn · Accepted solution]

solved this using the below... the logic was to apply a different condtion when the measure saw one week vs more than one week

weekly Attr = if(HASONEVALUE(Table[WEEK]),sum(Table[EXITS])/sum(Table[HC]),sum(Table[EXITS])/(sum(Table[AVG. HC])/DISTINCTCOUNT(Table[WEEK])))

[v-stephen-msft]

Hi @shaashn ,

Glad you can find a solution. Please mark your own reply as a answer, more people will benefit.

Best Regards,

Stephen Tao

[shaashn]

I initially tried that. there was a 1-1 relationship where "week 1" related to "WK-1 1st jan - 7th jan" (example)

but that didnt work out

[speedramps]

Calendars should have 1:M relationship with fact tables.

It will work.  There are lots of calendar tutorials on You tube.

If you still need help then create some test data and save it on a share drive and post the link so we can help you.