cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

average of sum in a table

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.

1 ACCEPTED SOLUTION
Frequent Visitor

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])))
7 REPLIES 7
Community Support

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.

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Super User

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.

This will force 1 row per week as required.

Frequent Visitor

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])))
Community Support

Hi @shaashn ,

Best Regards,

Stephen Tao

Frequent Visitor

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

Super User

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.