Calculation of a mean value with previously aggregated values

[Anonymous]

Hello dear PowerBI Community,

I have a problem that I can't solve on my own, so I'm turning to you, the experts here in the forum. I'll do my best to describe everything as well as possible, but maybe you'll bear with me if something doesn't fit 100%, as it is my first post.

Problem description:
I am performing data quality checks on various tables and store the numerical result with the name and the exact date of testing in a table in a PostgreSQL database. While most of the checks are only done once a month and the result table is therefore only extended monthly, there is also a table that is checked daily. What I want to do now is to aggregate the daily check to a monthly average so that they have the same time reference as the other values. This is not a problem as far as it goes and can be solved by simple filtering and averaging for this specific table. In a second step, however, I would now like to calculate the average per month for all tables, including the new aggregation level of the daily table. Unfortunately, I can't find a way to do this well. So far, the average is always distorted because the 30 or so individual daily entries are not first aggregated into a monthly value and then included in the final average calculation, but are directly included in the overall average.

Here is a small example to explain the problem:

Table-Name	Date	Score
Daily	01.01.2020	3
Daily	02.01.2020	4
Daily	03.01.2020	3
...	...	...
Daily	31.01.2020	4
Monthly_1	31.01.2020	8
Monthly_2	31.01.2020	8
Monhtly_3	31.01.2020	9

Let us assume, the daily table averages to a monthly Score of 4.

Now, the overall monthly average i want to calculate is as follows: (4 + 8 + 8 + 9)/4 = 7,25

However, using PowerBI tables to calculate the average all the daily table scores are taken individually (3+ 4+ 3+ ... + 4 + 8 + 8 + 9)/35 = <7,25

What i am using:

I am doing all my calculations using the PowerBI "Table" visualization but can't find a way to only use the freshly aggregated monthly values of the daily table instead of the individual daily tables.

Do you have any suggestion on how to solve this problem?

[Greg_Deckler]

@Anonymous 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

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Anonymous]

Dear @Greg_Deckler,

thank you very much for your quick reply and the possible solution to my aggregation problem!
However, I am now quite unsure about how to actually apply the help you provided. Is it possible to change the average calculation in a "table" visualisation and if so, how exactly do I go about it?

Kind regards
Janbosslet