Matrix visual: Averaging Year/Month results based on the average daily ones

[gkaloferov]

Hello Community,

I have to calculate the average work done by a user per day, month and year. Therefore, I have the following table (actually it is a VIEW):

DATE / USER_ID / NAME (blurred due to privacy reasons) / TYPE / QUANTITY / COUNT_ITEM

The table has a relationship with a Calendar table.

The following calculations have to be done:

1. Calculate the sum of rows for a given day and which represents the work done by all users from different types. This is done by summing the column COUNT_ITEM since it summarizes the raw data and although it is not shown, there are rows where COUNT_ITEM numbers are greater than one.

The matrix below shows the first calculation per day, month and year. It is differentiated by the column TYPE (possible values are 1,2,3 and 4):

2. The second calculation is to consider the unique users who worked on a given day. Therefore, the DISTINCTCOUNT is applied to column USER_ID.

For example for date 13.11.2018 for TYPE "1 "the results are: 529 rows processed by 3 unique users.

3. Third calculations: Finding the average rows processed by а given user or otherwise: SUM(COUNT_ITEM)/DISTINCTCOUNT(USER_ID).

For example: for the date 13.11.2018 for TYPE "1 "the average result is about 176.33, for type 2 on the same date the average result is 197.25 and so on. These results are correct, however the monthly and annual ones are biased.

For example, in November for TYPE 2 the results are as follows:

7040 rows processed by 6 unique users which is equal to 1173.33 on average. However, the number 6 is biased due to the following reason: one user on a given day may be type 1, and another day is type 2, and so on. Although 6 unique users have worked throughout November, on а daily basis, six users have worked only on November 8th, but during the rest of the time - four to five. Therefore, 1173,33 is not the correct monthly average. The correct would be: Averaging the daily users and then dividing the sum of count items on it.

For example:

On average the users worked in November (for type 2) are:

4.555555556

7040 count items divided by 4.555555556 is approximately equal to 1545.36.

So my question is: How to average monthly/annual (subtotals) and totals results based on daily results.

I tried many different approaches: quick measures, AVERAGEX, variables for the daily results which are then submitted as an argument for the monthly results. No results at all and I am in a deadlock.

Thank you in advance.

[Greg_Deckler · Accepted solution]

I believe that the Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[gkaloferov]

Thank you very much!! You're the best!