cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

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

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?
2 ACCEPTED SOLUTIONS
Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

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:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

11 REPLIES 11
Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

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

Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

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 ?

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

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.

Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

WOW @MFelix !! Works beautifully !

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

Helper I

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

Super User

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:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

Thanks !! This is on point ! 🙂

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors