Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |