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,
How do I make the sume by month correctly took each value?
In the graph, the top table is the summary and the button table is "performance".
Measures were calculated as:
Sales 48h_test =
VAR date_online = MAX(performance[Date0])
VAR code =VALUES(performance[Discount name])
RETURN
CALCULATE(
SUMX(FILTER(orders, orders[discount_name]=code), orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2))
Sales 48h sum_test = SUMX(VALUES(performance[Discount name]), [Sales 48h_test])
But seems that if the code re-appear, in the summary table only took the MAX date of that code rather than sum up all of them.
How do I adjust the MAX(performace(date0) parameter?
Solved! Go to Solution.
@evolve_Tricia Guessing, but I think that your VALUES is your issue:
Sales 48h_test =
VAR date_online = MAX(performance[Date0])
VAR code =VALUES(performance[Discount name])
RETURN
CALCULATE(
SUMX(FILTER(orders, orders[discount_name]=code), orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2))
So, when you are comparing orders[discount_name]=code and code has multiple values, that's likely the cause. Maybe try this:
Sales 48h_test =
VAR date_online = MAX(performance[Date0])
VAR code =VALUES(performance[Discount name])
RETURN
CALCULATE(
SUMX(FILTER(orders, orders[discount_name] IN code), orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2))
@ me in replies. Thanks.
@evolve_Tricia , You can try like
sumx(values(Table[Month]), lastnonblankvalue(Table[Date],sum(table[YM_TEST])))
But in this case, it will not give the values for other dates
@evolve_Tricia Sorry, having trouble following, can you post sample data as text and expected output?
Might be having an issue with measure aggregation or maybe you need MM3TR&R? This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi @Greg_Deckler
Thanks for you explanation.
Here is the sample data "performance", I need to evaluate the performance of each promotion. Same discount name might be applied to different promotions.
Revenue of sales 48 hours after each promotion were calculated with this measure:
Sales 48h_test =
VAR date_online = MAX(performance[Date0])
VAR code =VALUES(performance[Discount name])
RETURN
CALCULATE(
SUMX(FILTER(orders, orders[discount_name]=code), orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2))
This is how I calculated monthly and quarterly sum:
Sales 48h sum_test = SUMX(VALUES(FR_Youtubers_performance[Date0]), [Sales 48h_test])
Sales 48h sum by ym_test = SUMX(VALUES(date_table[year-mon]), [Sales 48h sum_test])
The first 2 months seems fine. However, when there were duplicated dates (but different codes and promotions), there will be error indicating a table of multiple values was supplied where a single value was expected.
Which measure should I correct?
@evolve_Tricia Guessing, but I think that your VALUES is your issue:
Sales 48h_test =
VAR date_online = MAX(performance[Date0])
VAR code =VALUES(performance[Discount name])
RETURN
CALCULATE(
SUMX(FILTER(orders, orders[discount_name]=code), orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2))
So, when you are comparing orders[discount_name]=code and code has multiple values, that's likely the cause. Maybe try this:
Sales 48h_test =
VAR date_online = MAX(performance[Date0])
VAR code =VALUES(performance[Discount name])
RETURN
CALCULATE(
SUMX(FILTER(orders, orders[discount_name] IN code), orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2))
@ me in replies. Thanks.
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |