Forum Discussion

gco's avatar
gco
Resolver II
6 years ago
Solved

Adding MAXX

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

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • dax's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      gco
      Resolver II
      6 years ago

      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