Average of a column based on criteria

[Surekha_PM]

Hello,

I am trying to perform the AVERAGEIFS function logic which we use in excel in PowerBI. I have one calendar date table which has unique date. Another table has duplicate date; basically, a single date has multiple category. Now, I want to know the average of the values of the dates in the category table for each date. For example, see below:

Table A (Calendar Date)

1-May-24

2-May-24

3-May-24

4-May-24

5-May-24

6-May-24

7-May-24

8-May-24

9-May-24

10-May-24

Table B;

day	Category	Value
1-May-24	A	100
1-May-24	B	0
1-May-24	C	0
1-May-24	D	100
1-May-24	E	100
2-May-24	A	100
2-May-24	B	100
2-May-24	C	100
2-May-24	D	100
3-May-24	A	100
4-May-24	A	100
5-May-24	A	100
6-May-24	A	100
7-May-24	A	100
8-May-24	A	100
9-May-24	A	100
10-May-24	A	100

Once I do the average I will do the sum of values column (after doing average) and divide it by number of days.

Can anyone suggest please.

Thanks & Regards,
Surekha

[jgeddes]

If you are looking for a result like the following...

You can create the following measures.

Category Average = 
var _vTable = 
SUMMARIZE(
    'Table',
    'Table'[Category],
    "_average", AVERAGE('Table'[Value])
)
RETURN
SUMX(
    _vTable,
    [_average]
)

Count of Days = 
CALCULATE(
    DISTINCTCOUNT('Table'[day]),
    ALL('Table')
)

avg per day = 
DIVIDE(
    [Category Average],
    [Count of Days],
    0
)

If you are looking for a different result please give an example of your desired output.

[Surekha_PM]

Hi Jgeddes,

I tried this function but it is not given me any result. It is shwing blank. Any other way to solve this?

Regards,
Surekha