cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bestmbaman Member
Member

SUM, Filter and MAX combined

Hi,

I have the following formula which perfectly returns the sum of the roles row. However, I don't understand how it works because my roles column is not numeric values:

Sum for roles= CALCULATE(SUM(Table1[Value]),FILTER(Table1, Table1[Role]=max(Table1[Role])))

following is an screenshot of the dataset:

MAXRoles.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SUM, Filter and MAX combined

Hi ,

When you use the Max function on a filter and you add the measure to a table what happens is that the context of the table is picking up the sum of the maximum value of the column you select.

Measure are calculated taking the visual, filters and formulas as a context calculation so placing measures within certain visuals can change your result.

In this case since the total columns is picking up the last role and making the sum of that for each type. So since your last role is Mother2 if you look at role and type for Mother2 it match your total column.

To take out the totals turn it off on the matrix option.

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




6 REPLIES 6
Super User
Super User

Re: SUM, Filter and MAX combined

Hi @bestmbaman ,

 

In DAX the MAX function returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.

 

This is because of the way the internal engine of DAX works, so when comparing strings values it works correctly.

 

Check this documentation below:

https://dax.guide/max/

https://docs.microsoft.com/en-us/dax/max-function-dax

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bestmbaman Member
Member

Re: SUM, Filter and MAX combined

I know what MAX function is doing for numerical values @MFelix , but if you take a closer look at my dataset, they are all TEXT (the Roles column) and not numbers! So, what is MAX function is doing here? Because when I use it in my table visual, it simply returns the last value in the last column before total  column which is the result of the measure Sum for roles. Please check out the Screen shot below:

TotalMeasureAddedColumn.JPG

In the above table I only need the last row Total, but it also automatically visualizes the Total at the last column!

What should I do to get rid of this Total column @MFelix ?

Super User
Super User

Re: SUM, Filter and MAX combined

Hi ,

When you use the Max function on a filter and you add the measure to a table what happens is that the context of the table is picking up the sum of the maximum value of the column you select.

Measure are calculated taking the visual, filters and formulas as a context calculation so placing measures within certain visuals can change your result.

In this case since the total columns is picking up the last role and making the sum of that for each type. So since your last role is Mother2 if you look at role and type for Mother2 it match your total column.

To take out the totals turn it off on the matrix option.

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: SUM, Filter and MAX combined

Hi,

Does this work

=IF(HASONEVALUE(Table1[Role]),your_measure,BLANK())

Hope this helps.

bestmbaman Member
Member

Re: SUM, Filter and MAX combined

@MFelixIf I don't pick the Total Measure in my visual then how I can show the the sum of Mother1,Mother2,etc. for each column!? The primary reason of calculating Total Measure for me was to show SUM of my columns but it also does the same for  the rows ! which is reduntant and mislealeading

What should I do to have only SUM of each column?

 

Super User
Super User

Re: SUM, Filter and MAX combined

Hi @bestmbaman,

On the matrix you can go to totals and turn off total on rows.

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!