Forum Discussion
Average column in Matrix table
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:
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
4 Replies
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#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebcThank 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
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,.
