Calculate sum of max value

[Anonymous]

Hi,

We are making a notification report, but we are struggling with the following formula.

We want the sum of the max days open per notification.

A notification can appear multiple times in the dataset, we want to count only once the days open per notification and then take the sum over all notifications.

We've tried the following measures they don't return the required result:

SUMX(GROUPBY(Table;Table[Notification]; "Notification Summarized";SUMX(CURRENTGROUP();Table[Days claim open]));AVERAGE(Table[Days claim open]))

SUMX(DISTINCT(Table[Notification]);CALCULATE(AVERAGE(Table[Days claim open])))

Thanks for helping us out!

Valérie

[Anonymous · Accepted solution]

@Anonymous try using all except

1)  Max Days claim open = CALCULATE(MAX[Days claim open or Days open), ALLEXCEPT(Notification))

2) in the card or table just drop the Max Days claim open field it should sum it up by default.

[Anonymous]

Hello,

I Have problem ,  I have a chart bar when i want show max of the summary for Stage, but show max of column

help me!!

[Anonymous · Accepted solution]

@Anonymous try using all except

1)  Max Days claim open = CALCULATE(MAX[Days claim open or Days open), ALLEXCEPT(Notification))

2) in the card or table just drop the Max Days claim open field it should sum it up by default.

[Anonymous]

Hi @Anonymous,

Thank you for the reply, but this is not exactly want we want to achieve as the total sum in a table with notification in the rows is incorrect.

[v-piga-msft]

Hi @Anonymous,

If it is convenient, could you share some data sampe and your desired output so that we could understand your requirement better and get the solution?

Best  Regards,

Cherry

[Anonymous · Accepted solution]

All,

Inspired by the comment of @Anonymous, we've found the solution:

 SUMX(DISTINCT(zClaimsQuery[Notification]);CALCULATE(AVERAGE(zClaimsQuery[Days claim open]);ALLEXCEPT(zClaimsQuery;zClaimsQuery[Notification])))

Thanks for the help!

[v-piga-msft]

Hi @Anonymous,

Congratulations!

Only thing that you'll have to notice, just always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry