Adding MAXX

[gco]

Hi there,

I have a table that contains Accounts, OpenDate, and NumberofProds.  NumberofProds is calculated as MAXX(tablename, tablename[OpenProds].  And i am now trying to get a sum of all NumberofProds for the whole table.  Your help is appreciated.

sample:

Accounts   OpenDate                NumberofProds

1                  11/12/2019                     2

2                  11/13/2019                     1

3                   11/13/2019                    3

Total_NumofProds = 6

if i enable the total for the table, the total showing the maximum row value.

Thank you

Glen

[dax · Accepted solution]

Hi gco,

You could try below measure to see whether it work or not

My  sample data is like below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMLTUN9Q3BDJNlWJ1MASNUAWNwIKG2ASNUQWNwYJm2AQh2pOw2Z6EzfYkbLajCaJpR7Y9CcP2WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"date", type date}, {"amount", Int64.Type}})
in
    #"Changed Type"

Measure 4 =
IF (
    HASONEVALUE ( 'Table (3)'[id] ),
    MAXX ( 'Table (3)', 'Table (3)'[amount] ),
    SUMX (
        SUMMARIZE (
            'Table (3)',
            'Table (3)'[id],
            'Table (3)'[date],
            "max", MAX ( 'Table (3)'[amount] )
        ),
        [max]
    )
)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gco]

Thanks @dax  and @Anonymous ,

Thanks to both of you.  It seems like Zoe's (@dax) measure gave me the most accurate result.  Now i just have to learn how the measure is doing the calculations so that i understand the logic better!  Thanks again

Glen

[Anonymous]

Hi @gco ,

the only way I can suggest:

NumberofProdsSum = 
var MaxTable = GROUPBY('Table', [Accounts], [OpenDate], "NOP", MAXX(CURRENTGROUP(), [OpenProds]))
return SUMX( MaxTable, [NOP])

This returns correct numbers for each row and total. The downside of the formula, I think, is that you need to group it to the exact columns in the resulting table visual.

Kind regards,

JB