Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RogerSteinberg
Post Patron
Post Patron

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

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

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

 

 

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...

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.

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

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.
Anonymous
Not applicable

Thanks Lin. Good answer!
Anonymous
Not applicable

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. 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.