Average on Average over category

[VigneshMani]

we need to take average % value(measure not a column value) of each month for each cat 3. then we need to take average of cat 3 for each Category 2. and finally need to take average of cat 2 for each cat 1. 

Thanks in Advance!

[PaulOlding]

Hi @VigneshMani 

Here's a measure that builds interim tables to get average at cat 3 level first, then at cat 2 level.

Average % Value = 
VAR _AverageCat3 = 
ADDCOLUMNS(
    SUMMARIZE('Table','Table'[cat1],'Table'[cat2], 'Table'[cat3]),
    "@AverageCat3", CALCULATE(AVERAGE('Table'[% Value]))
)
VAR _AverageCat2 = 
ADDCOLUMNS(
    SUMMARIZE(_AverageCat3,[cat1],[cat2]),
    "@AverageCat2", AVERAGEX(FILTER(_AverageCat3, 'Table'[cat1] = EARLIER('Table'[cat1]) && 'Table'[cat2] = EARLIER('Table'[cat2])), [@AverageCat3])
)
VAR _Result = 
AVERAGEX(_AverageCat2, [@AverageCat2])
RETURN
    _Result

In a matrix that gets you this

[VigneshMani]

Hi @PaulOlding 

% value is a measure not a column value in a table. 

if possible can you attach the sample file ?

[PaulOlding · Accepted solution]

Hi @VigneshMani 

Perhaps this revised measure will work.  There's a slight change in the formula for @AverageCat3 to use your % Value measure.

Average % Value = 
VAR _AverageCat3 = 
ADDCOLUMNS(
    SUMMARIZE('Table','Table'[cat1],'Table'[cat2], 'Table'[cat3]),
    "@AverageCat3", CALCULATE(AVERAGEX(VALUES('Table'[Month]), [% Value]))
)
VAR _AverageCat2 = 
ADDCOLUMNS(
    SUMMARIZE(_AverageCat3,[cat1],[cat2]),
    "@AverageCat2", AVERAGEX(FILTER(_AverageCat3, 'Table'[cat1] = EARLIER('Table'[cat1]) && 'Table'[cat2] = EARLIER('Table'[cat2])), [@AverageCat3])
)
VAR _Result = 
AVERAGEX(_AverageCat2, [@AverageCat2])
RETURN
    _Result

[ValtteriN]

Hi @VigneshMani ,

I am completely sure I follow, but you should be able to accomplish this by using a simple AVERAGE and depening on your visulization type you can use calculate + all to ignore date filters.

Example data:

DAX (I created a relationship between calendar and the example data, before this):

Monthly AVG = //this is a simple average

AVERAGE('Table (7)'[Value])

You can utilize this to get cat2 or cat3 averages (use table and matrix visual respectively):

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[VigneshMani]

Hi @ValtteriN 
Thanks for the reply. our unerstandings are differnt. we need to do Average on Averge on average. 

and the % values is a measure.  update the excel with color codes for better undersatanding!