Measure that used in matrix. Average by column hieraphy and Sum of Averages by rows hieraphy

[yakovlol]

Hello, community,

I have an unusual task to calculate measures in matrix table, that consists of 5 level hierarchy in rows and 3 level hierarchy in columns.

In columns, I have dates-weeks- month. And what I need is when dates are aggregated into week it shows the average
and when rows collapse those averages become sum of averages.

The question how to do calculation that will calculate the SUM of averaes by hierarchy

[Selva-Salimi · Accepted solution]

Hi @yakovlol ,

in the case that @Shivu-2000 solution does not work you can try this....

to show in the value option of your natrix you need to write a measure as follows:

measure sum_average := var tbl = summarize(your_table, the name of column in the row of matrix , "Avg" , average  (what you have in you matrix right now) )

return

sumx(tbl,avg)

in this case if you are in the hierarchey level, the sum is the same as average and in top level they would be sum up.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

[yakovlol]

Hello @Selva-Salimi Thanks for your approach. this is exactly what I need.

But i have another problem, maybe you know how to handle Average measure to calculate with blanks values.

But the real problem is that this blank rows in my table doesn't exist. they are only seen in my matrix table . I used this measure, but seems it doesn't work

AVERAGEX(KEEPFILTERS(ALL(table[Date])),[Sales]))

[Selva-Salimi]

@yakovlol 

Maybe adding zero to sales calculation ([sales] +0) work for you. if not share more details.

If this post helps, then I would appreciate a thumbs up 👍

[Shivu-2000]

Hi @yakovlol 

So according to my understanding you have a matrix table that have 5-level row hierarchy and a 3-level column hierarchy (dates, weeks, and months), and you want to calculate averages when dates are aggregated to weeks and sums of averages when rows are collapsed (correct??).

To do so firstly you will have to Create Calculated Columns as

• Week Number: Create a calculated column to extract the week number from the date column.
• Month Number: Create a calculated column to extract the month number from the date column.

Than Create Measures:

• Average Value: Create a measure to calculate the average of your desired value. For example:

  Average Value = AVERAGE([YourValue])

• Sum of Averages: Create a measure to calculate the sum of averages. Use the CALCULATE function to modify the filter context:

  Sum of Averages = CALCULATE(
      [Average Value],
      ALLEXCEPT(YourTable, YourTable[Week Number])
  )

  This formula calculates the average value for each week and then sums them up, ignoring the filters applied at the row level.

 Configure Matrix Table:

• Row Hierarchy: Add your 5-level hierarchy to the rows of the matrix table.
• Column Hierarchy: Add your 3-level hierarchy (dates, weeks, months) to the columns.
• Measures: Add the Average Value and Sum of Averages measures to the values area.

For Example:

If your table has columns Date, Region, Category, Product, Sales, and you want to calculate the average sales per week and the sum of averages per region, you might use the following measures:

Code snippet

Week Number = WEEKNUM([Date])
Month Number = MONTH([Date])

Average Sales = AVERAGE([Sales])
Sum of Average Sales = CALCULATE([Average Sales], ALLEXCEPT(YourTable, YourTable[Week Number]))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!