cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gco Resolver II
Resolver II

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
Community Support

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
Super User I
Super User I

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
Community Support

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 Resolver II
Resolver II

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 Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors