Average of measure

[Wolf01]

Hello, I would like to ask you for help. I believe, that the solution is easy, but I can't figure it out.

Basically I need to calculate an average of a measure. I tried to give you as detailed explanation as I can. Unfortunately, I can't share the real file.

My model looks like this and here are also samples of dummy data for Var1Tab and Var2Tab:

Matrixes of Var1 and Var2 look like this: 

And now I created this measure:

MyMeasure = CALCULATE(DIVIDE(SUM(Var1Tab[Var1]);SUM(Var2Tab[Var2])))+0

Matrix with MyMeasure looks like this:

And this is my problem, because I need a different result in column Total. I need this:

To achieve desired result above, I calculated new table:

CalculatedTab = SUMMARIZECOLUMNS(PersonTab[PersonID];'Calendar'[Date].[Year]; 'Calendar'[Date].[Month]; "NewVar";Var1Tab[MyMeasure])

When using NewVar in matrix and choosing Average in matrix settings it is doing exactly what I need. But another problem occurs. I am not able to filter by PersonTab[PersonID].

So my question is, how to create matrix I need (shown above) and keep the possibility to filter by PersonTab[PersonID]?

Thank you for any help!

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[Wolf01]

Hello Greg_Deckler,

Thank you for your article. I went though it and need to go through it more times 🙂

[amitchandak]

Can you share sample data and sample output in excel

[Wolf01]

Hello, thank you for your reply.

Here are links to excel data file and power bi file:

https://drive.google.com/file/d/1bBCAYXqjFKBi-TvJz4cGnFX5mpLTaCsU/view?usp=sharing

https://drive.google.com/file/d/1hRU8sfDA-BmQobuMIgxwYyZe1ubjQ915/view?usp=sharing