Average column in Matrix table

[Anonymous]

Hi All,

I need to include Average column in matrix table that should look like the table below. Can anyone show how I can include the average column in the matrix table?

[Updated] Also, the week columns are dynamic such that I have a Week slicer to determine the latest week and the Matrix will show the rolling 5 weeks based on the selection (i.e., If Week slicer is filtered to Wk2, Matrix table will display Wk6 to Week 2. If slicer is filtered to Wk 1, Matrix table will display Wk5 to Wk 1).

[Updated] I have a measure that is created to display rolling 5 weeks in Matrix table. 

Actual_5wks: 

CALCULATE([Actual], FILTER(Dim_Period, Dim_Period[Week Index]>=([Selected_WeekID]-4) && Dim_Period[Week Index]<= [Selected_WeekID]))

To calculate Average : Total / Count of no of week value (i.e., Avg of Product B --> 20/3 = 6.7)

Rows: Country, Products

Columns: Week

Values: Units

P/S: I'm unable to share the pbix ><

Regards,

BK

[Newcom]

Hi I have similar question to ask here,

using metric table, First it is filtering by week and then each week have 3 more column and value. I would like average of eachc column.

Could anyone help me with that please,.

[amitchandak]

@Anonymous , You can get a measure like this

calculate(divide(sum(Table[unit]),distinctCOUNT(Table[Week])), allselected(Table[week]))

you can replace column total with help from isinscope

if(isinscope(Table[Week]), Sum(Table[Unit]),calculate(divide(sum(Table[unit]),distinctCOUNT(Table[Week])), allselected(Table[week])))

or

if(isinscope(Table[Week]), Sum(Table[Unit]),calculate(divide(sum(Table[unit]),distinctCOUNT(Table[Week]))))

refer:

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

[Anonymous]

Thank you for your reply! I do realise I did not explain my scenario clearly. Have updated the post with more info.

I actually have a list of weeks in the Dim_Period, however, I only want to display rolling 5 weeks based on the selection in Week slicer (i.e., If Week slicer is filtered to Wk2, Matrix table will display Wk6 to Week 2). For this, I already have a measure (Actual_5wks)to display the rolling 5 weeks data.

[amitchandak]

@Anonymous , That last column is kind of a hybrid table need. I shared links for a workaround and the idea.

Or each week need to me a measure