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

[RogerSteinberg]

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?

[Anonymous]

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

[RogerSteinberg]

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

[v-lili6-msft]

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

[RogerSteinberg]

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

Dummy data:

order_id_trx	kind	status	amount	created_at	orderID	financial_status	OrderDate
55	sale	success	31.49	6/19/2019	55	refunded	6/19/2019
55	refund	success	31.49	7/9/2019	55	refunded	6/19/2019
11	sale	success	23.13	6/21/2019	11	refunded	6/21/2019
11	refund	success	23.13	6/21/2019	11	refunded	6/21/2019
12	sale	success	31.61	6/22/2019	12	refunded	6/22/2019
12	refund	success	31.61	6/28/2019	12	refunded	6/22/2019
12	refund	pending	31.61	6/24/2019	2	refunded	6/22/2019
23	sale	success	62	6/30/2019	23	refunded	6/30/2019
23	refund	success	62	6/30/2019	23	refunded	6/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

[v-lili6-msft · 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:

Best Regards,

Lin

[Anonymous]

Thanks Lin. Good answer!

[Anonymous]

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. 

[RogerSteinberg]

Hey unfortunately it doesn't work @Anonymous