How to find sum of max values in a column and filter it?

[FeezaAga]

I have a table that has mutiple values of "OPCount" as rows for each Faciliy name and each date. So i am summing them up to show the max for each day for each facilty. 

Below is what i am using to summarize my rows(Please tell me if this is correct):

1OPCount =

IF(

-- NORMAL LINE---

MAXA(Query1[OPCount]),

-- TOTAL LINE ---

SUMX(

VALUES('Query1'[InvDate1]),

CALCULATE(

MAXA(Query1[OPCount])

)

)

)

This is how i get my values - ie: max of among the rows. Butt, I need it to show as total of each column.

Can you'll suggest a better way to do this so that i clearly get the total of each column whatever filter i may use?

[MFelix · Accepted solution]

Hi @FeezaAga ,

To what I can understand from your data and the result you present is that the values you are picking up are the maximum values for each day correct?

I added a new value of 5 to facility A and day 1 in order to get two values for the same date:

Then I made some changes to the measure adding the factory to the sumx:

1OPCount =
IF (
    HASONEFILTER ( 'Query1'[EachDay of month ] );
    MAX ( 'Query1'[OPCount] );
    SUMX (
        SUMMARIZE (
            'Query1';
            'Query1'[EachDay of month ];
            Query1[FacilityName];
            "@Max"; MAX ( 'Query1'[OPCount] )
        );
        [@Max]
    )
)

When making use of aggregate functions you need to refer to the columns you need, in this case the result of the summarize table is a table that picks up the value for each facility and each day and a single value for the OPCount.

Having the previous syntax what you are getting is a table with days and a value per each day so you only get the 551 has you final result, that is the higher value at the day level.

Check image below and PBIX file attach.

As you can see on facility A day 1 i have 11 and the 5 is not consider and if you sum all the days that are presented you get 1.093.