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.

[MFelix]

Hi @FeezaAga ,

Try the following measure:

Maximum Value =
IF (
    HASONEFILTER ( 'Table'[cat] );
    MAX ( 'Table'[Value] );
    SUMX (
        SUMMARIZE ( 'Table'; 'Table'[cat]; "@Max"; MAX ( 'Table'[Value] ) );
        [@Max]
    )
)

Be aware that you must adapt the name of the columns to your needs.

[FeezaAga]

Thanks so much for the response @MFelix . It seems to give me the values exactly the way i got them before, using my measure.

1OPCount =

IF (

HASONEFILTER ( 'Query1'[InvDate1] ),

MAX ( 'Query1'[OPCount] ),

SUMX (

SUMMARIZE ( 'Query1', 'Query1'[InvDate1],"@Max", MAX ( 'Query1'[OPCount] ) ),

[@Max]

)

)

 

RESULT

[MFelix]

Hi @FeezaAga 

The column you must use on the summarization part that I call CAT is the first column on your matrix.

So if the InvDate1 is the lowest detail and you must use the column above the invdate looking at your first image is the FacilityName.

[FeezaAga]

Thank you @MFelix . if  I use FacilityName it gives me the max value in the whole month for each facility and then sums in the total. I need all the max for each day to be summed up for each facility. Can you think of something for this ?

Thanks in Advance.

[MFelix]

Hi @FeezaAga ,

This is possible but if you can share a sample of your data is easier.

Please see this post regarding How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

[FeezaAga]

Thank you @MFelix .Below is just three of the main columns of my raw data in picture for this measure...with the sample data.

FacilityName	EachDay of month	OPCount
A	1	11
A	2	13
B	1	254
B	2	297
C	1	157
C	2	220
D	1	89
D	2	52

Below is what I get when i try to get the max and sum the values. Final 'Total' value gives max of the column which i need it to be the actual total.

[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.

[FeezaAga]

WOW @MFelix !! Works beautifully !

Thank you so much !! It is perfect !! 😀 Really appreciate your help!

[FeezaAga]

Wonder why it does not work when I need to find the total for a single day.

[MFelix · Accepted solution]

Hi @FeezaAga ,

Has refered measures are based in context when in the syntax is refered HASONEFILTER(Table[EachDay]) means that it will check if there is a single value selection for EachDay son when it has one day then it will give MAX other wise is the sum of the values.

When you are filtering on a single day the HASONEFILTER is always true so returns always the maximum value and not the sum try to make the following change to your measure:

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

Check PBIX file attach:

[FeezaAga]

Thanks !! This is on point ! 🙂