Total of measure wrong - average of average

[Anonymous]

Dear community,

I'm having a hard time creating a measure that should do the following:

• getting the max value grouped by 2 columns
• getting the average of the calculated max value
• getting the correct total of the calculated average

As an example I will use the following table:

Person	Location	Date	Success
Person A	Location 1	2021-08-02	0
Person A	Location 2	2021-08-02	1
Person A	Location 3	2021-08-02	1
Person A	Location 3	2021-08-02	1
Person A	Location 3	2021-08-02	0
Person B	Location 4	2021-08-02	1
Person B	Location 4	2021-08-02	1
Person B	Location 5	2021-08-02	0
Person B	Location 5	2021-08-02	0
Person B	Location 6	2021-08-02	1
Person C	Location 7	2021-08-02	0
Person C	Location 7	2021-08-02	1

The measure should return the maximum value from the column "Success" grouped by Location and Date.

This means the following values should be user for Person A:

• Location 1 => Success 0
• Location 2 => Success 1
• Location 3 => Success 1 

The average (in percent) of those 3 locations should be 66,67%.

My measure looks like this:

Average of Success per Person and Date =
AVERAGEX(
    SUMMARIZE ( 'Fact', 'Fact'[Location], 'Fact'[Date] ),
    CALCULATE(MAX('Fact'[Success])
))

When i use this measure within a table visual it shows the correct average per Person but the total is wrong.

The total should be 77,78% (Average of 66,67%, 66,67% and 100%). Unfortunately the visual shows a total of 71,43%. 

Can anyone help?

Thanks in advance

[amitchandak]

@Anonymous , I think you need to use person in this , that is part of visual

AVERAGEX(
SUMMARIZE ( 'Fact', 'Fact'[person], 'Fact'[Date] ),
CALCULATE(MAX('Fact'[Success])
))

[Anonymous]

Hi @amitchandak , using the person in the measure leads to another wrong result

[v-easonf-msft · Accepted solution]

Hi,  @Anonymous 

Try to add a new measure as below:

New_Average of Success per Person and Date = 
AVERAGEX (
    SUMMARIZE (
        'Fact',
        'Fact'[Person],
        "Measure1", [Average of Success per Person and Date]
    ),
    [Average of Success per Person and Date]
)

Best Regards,
Community Support Team _ Eason