Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have several duplicates of order ids so to get the returns for each day i used the following function :
Solved! Go to 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
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
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") )
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |