Sums aggregated by month only took the largest value in the group not each value

[evolve_Tricia]

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? 

[amitchandak]

@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

[Greg_Deckler]

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

MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

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.

[evolve_Tricia]

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?

[Greg_Deckler · Accepted 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]

@Greg_Deckler 
Work like a charm, thanks so much. 🙂