SUM, Filter and MAX combined

[Anonymous]

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:

[MFelix]

Hi @Anonymous ,

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

[Anonymous]

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:

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 ?

[Ashish_Mathur]

Hi,

Does this work

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

Hope this helps.

[MFelix · Accepted solution]

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

[Anonymous]

@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?

[MFelix]

Hi @Anonymous,

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

Regards,
MFelix