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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Adding MAXX

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.

 

View solution in original post

3 REPLIES 3
jborro Member
Member

Re: Adding MAXX

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

Community Support Team
Community Support Team

Re: Adding MAXX

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.

 

View solution in original post

gco Member
Member

Re: Adding MAXX

Thanks @dax  and @jborro ,

 

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,236)