Forum Discussion
Average column in Matrix table
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
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.
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