cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Getting the max for each row but total row showing the highest value

Hi all,

 

I have several duplicates of order ids so  to get the returns for each day i used the following function :

CALCULATE(
  MAX(Query1[amount]),
  USERELATIONSHIP(Query1[created_at], DateTable[Date]),
   Query1[kind] = "refund",
   Query1[status] = "success"
)

The values are good for each day. However, the total row shows the MAX value in that column (obviously due to the MAX function in my expression).
 
I would like my total row to sum the whole column. Especially, because i need to use that measure for a Net Sales function: Gross Sales - Refunds
 
This doesn't work as it takes the MAX amount for refunds.
 
Any idea?
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Getting the max for each row but total row showing the highest value

hi, @RogerSteinberg 

For your case, just adjust your formula as below:

new refund = 
VAR Summary =
 CALCULATETABLE(SUMMARIZE (
        Query1,
        Query1[orderId],
        "MaxValue", CALCULATE (
            MAX ( Query1[amount] ),
            Query1[kind] = "refund",
            Query1[status] = "success"
        )
    ),USERELATIONSHIP ( Query1[created_at], DateTable[Date] ))
RETURN
    SUMX ( Summary, [MaxValue] )

Result:

2.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
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

8 REPLIES 8
Anonymous
Not applicable

Re: Getting the max for each row but total row showing the highest value

Hi @RogerSteinberg 

What if you use a SUMMERIZECOLUMN function and get the Max amount with all the filters you desire and then use Calcuate function to SUM the amount column?

Thanks

 

 

Re: Getting the max for each row but total row showing the highest value

well this is what i tried:

refund =
VAR Summary =
    SUMMARIZE (
        Query1,
        Query1[orderId],
        "MaxValue", CALCULATE (
            MAX ( Query1[amount] ),
            USERELATIONSHIP ( Query1[created_at], DateTable[Date] ),
            Query1[kind] = "refund",
            Query1[status] = "success"
        )
    )
RETURN
    SUMX ( Summary, [MaxValue] )

 but this gives me a different problem: the sum of the max values work but the USERELATIONSHIP line doesn't activate for some reason.  Hence, i get values based on the active column relationship...

Anonymous
Not applicable

Re: Getting the max for each row but total row showing the highest value

Refund =
VAR Summary =
    SUMMARIZE (
        Query1,
        Query1[orderId],
        Query1[kind] = "refund",
        Query1[status] = "success",
        "MaxValue", MAX ( Query1[amount] )          
        )
    )
RETURN
    Calculate( SUM( Summary[MaxValue] ), 
    Userelationship('Created Date, Datetable(Date)))

There might be syntax errors which needs to be corrected. 

Community Support Team
Community Support Team

Re: Getting the max for each row but total row showing the highest value

hi, @RogerSteinberg 

I have test on my side, it works well.

Could you please share a simple sample pbix file for us have a test?

 

Best Regards,

Lin

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

Re: Getting the max for each row but total row showing the highest value

Hey unfortunately it doesn't work @Anonymous 
powebbb.png

Highlighted

Re: Getting the max for each row but total row showing the highest value

How can i upload the file? @v-lili6-msft 

 

Dummy data:

order_id_trxkindstatusamountcreated_atorderIDfinancial_statusOrderDate
55salesuccess31.496/19/201955refunded6/19/2019
55refundsuccess31.497/9/201955refunded6/19/2019
11salesuccess23.136/21/201911refunded6/21/2019
11refundsuccess23.136/21/201911refunded6/21/2019
12salesuccess31.616/22/201912refunded6/22/2019
12refundsuccess31.616/28/201912refunded6/22/2019
12refundpending31.616/24/20192refunded6/22/2019
23salesuccess626/30/201923refunded6/30/2019
23refundsuccess626/30/201923refunded6/30/2019

 

DateTable:

 
DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2018,01,01),TODAY()-1),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "YearNumber", FORMAT([Date],"YYYY"),
    "MonthNumber", FORMAT([Date],"MM"),
    "Year/MonthNumber",FORMAT([Date],"YYYY/MM"),
    "Year/MonthName",FORMAT([Date],"YYYY-mmm"),
    "MonthNameShort",FORMAT([Date],"mmm"),
    "MonthNameLong",FORMAT([Date],"mmmm")
)

 
Relationships
powebbb2.png
Community Support Team
Community Support Team

Re: Getting the max for each row but total row showing the highest value

hi, @RogerSteinberg 

For your case, just adjust your formula as below:

new refund = 
VAR Summary =
 CALCULATETABLE(SUMMARIZE (
        Query1,
        Query1[orderId],
        "MaxValue", CALCULATE (
            MAX ( Query1[amount] ),
            Query1[kind] = "refund",
            Query1[status] = "success"
        )
    ),USERELATIONSHIP ( Query1[created_at], DateTable[Date] ))
RETURN
    SUMX ( Summary, [MaxValue] )

Result:

2.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
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

Anonymous
Not applicable

Re: Getting the max for each row but total row showing the highest value

Thanks Lin. Good answer!

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors