Forum Discussion
Create an Average row in matrix.
Hi All,
Thanks in advance. The following is where I am at when I try to design a custom dashboard.
I used data from three sources to build a matrix table. Under the total row, I also require an average row, so that I may see the daily average. The July month data will be in day-wise format if I drilldown.
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
expected result: = VAR _sum = SUM ( data_fact[value] ) VAR _total = CALCULATE ( SUM ( data_fact[value] ), ALL ( matrix_dim ) ) VAR _monthlyaverage = AVERAGEX ( ALL ( matrix_dim[Year-Month sort number], matrix_dim[Year-Month] ), CALCULATE ( SUM ( data_fact[value] ) ) ) RETURN SWITCH ( SELECTEDVALUE ( matrix_dim[Year-Month] ), "Total", _total, "Average", _monthlyaverage, _sum )
4 Replies
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
expected result: = VAR _sum = SUM ( data_fact[value] ) VAR _total = CALCULATE ( SUM ( data_fact[value] ), ALL ( matrix_dim ) ) VAR _monthlyaverage = AVERAGEX ( ALL ( matrix_dim[Year-Month sort number], matrix_dim[Year-Month] ), CALCULATE ( SUM ( data_fact[value] ) ) ) RETURN SWITCH ( SELECTEDVALUE ( matrix_dim[Year-Month] ), "Total", _total, "Average", _monthlyaverage, _sum )Can I ask what the interpretation of the average in the context of a row would be? Like you want to know average Influx, Production, etc per day for July and perhaps each month?
Because on a monthly roll up, if you have the total, average for July kinda loses meaning. But if you say for the whole year on average the Influx or Production was x units per month, that makes more sense in terms of valuable business information.
You might benefit more from transposing your matrix where the months are in columns and your product heirarchy is in rows.
