AVERAGEX and Cumulative Totals

[astautz]

I'm using two measures I don't quite understand, and they're not working together. I hope someone can help me see the problem.

First, I'm using an AVERAGEX to give me an average invoice value based on sales data at the line-item level. Each row in the data table is a single product on a single transaction, and one of its dimensions is invoice number. There are usually multiple rows that have the same invoice number (ie multiple products on one invoice). That measure looks like this:

Avg Invoice Value := AVERAGEX(
                                         SUMMARIZE ( 'Data',
                                                                 'Data'[Invoice #]
                                                               ),
                                         CALCULATE ( SUM ( 'Data'[Gross Profit] ), ALL('Products')
                                                             )
                                                   )

As I understand it, the SUMMARIZE is returning a list of Invoice #s, CALCULATE is summing the Gross Profit of each line item that has the invoice number in question, and then AVERAGEX is averaging those sums. I put in ALL('Products') because I wanted to be able to filter the invoices based on whether they contained a particular product, while still summing the profit of all the products on the invoice, not just the one I wanted to make sure to include.

This AVERAGEX function seems to work well on its own; at least, I don't see any obvious errors coming up in the visualizations. [Avg Invoice Value] is positive over the entire date range I'm looking at.

But then I'm trying to get a cumulative total for a different measure that depends on [Avg Invoice Value].

The measure in question is:

GP from Customers := [Customers] * [Conversion Rate] * [Avg Invoice Value]

where [Customers] is a sum of customer traffic, [Conversion Rate] is a hard-coded percentage, and [Avg Invoice Value] is the measure we looked at above. Because [Customers] is just a sum, and [Conversion Rate] is hard-coded, I'm pretty sure the problems with the cumulative total of [GP from Customers] come from the [Avg Invoice Value] measure.

My cumulative total measure is straight from DAX patterns. It looks like this:

Cumulative GP from Customers :=
                                                         CALCULATE (
                                                                              [GP from Customers] ,
                                                                              FILTER (
                                                                                           ALL ( 'Calendar'[Date] ),
                                                                                           'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
                                                                                          )
                                                                              )

The problem is that [Cumulative GP from Customers] will decrease over time, although in theory there are no values for [GP from Customers] that are negative. I'm certain that [Conversion Rate] is positive, and I'm pretty sure that [Customers] is positive, therefore any negative values getting fed into [Cumulative GP from Customers] must be coming from [Avg Invoice Value].

 

The black line is [GP from Customers] - one value per week, always positive. The teal line is [Cumulative GP from Customers], and as you see it starts behaving weirdly at the end of August - decreasing over the course of the week, when it should be plateauing. 

I'm wondering if there's something in the measures I've used (perhaps the filter context of the cumulative measure?) that is causing the problem.

Many thanks in advance!

[astautz · Accepted solution]

@v-yuezhe-msft

Lydia,

Thank you so much for your concern in helping me find a solution. I really appreciate it.

I think I've hit on the theoretical obstacle with that last explanation - namely, that because [Avg Invoice Value] is a measure that changes with the time context in which it's evaluated, it will never behave like I want it to in the Cumulative Total pattern. The Cumulative Total pattern as it stands can't capture 'snapshots' of a measure; it can only reevaluate a measure in a broader context. The unexpected behavior of the Cumulative Total comes from the fact that it's returning [Avg Invoice Value] as the average invoice value of all invoices up to the contextual present, regardless of what the average invoice might have been during any particular week.

As long as [Avg Invoice Value] stays relatively steady, the problem doesn't jump out, but it's always there. Right now I'm going to accept the strangeish behavior of Cumulative Total because it's close enough for my purposes. I'll just add the caveat that the Cumulative Total is not exactly equal to the sum of the weekly values.

All of which is to say, many many thanks, but I think I understand and accept the problem, and so I'm not going to share data or have you spend your time investigating.

For others who may run into similar issues, I hope my explanations have been clear.

-astautz

[astautz]

@Greg_Deckler

I'm sorry; I don't understand. Could you elaborate?

Where would the ALLEXCEPT go, and what would I "excuse" with it?

For all:

The problem here might be the way the cumulative total pattern works. In clearing the Calendar/Date filters and then redefining them as the time up to the contextual "present," the measure is *NOT* summing snapshots of the base measures over time, but rather recalculating them in one go in a new, expanded context. That's fine for sums of values - adding the number of visitors each week, for example - that "happened" once and are thus permanently associated with a date.

For an averaged value, though, - like my [Avg Invoice Value] - there is no fixed connection between the value and the date except for the context in which it's evaluated. Therefore changing the context - as the cumulative total pattern does - changes the value, and everything is thrown off.

Therefore:

Is there an alternative solution to Cumulative Totals that works like SUMX? Something that would truly add snapshot values over time, instead of redefining the filter context?

[v-yuezhe-msft]

@astautz,

Could you please share sample data of your  table that can be copied and pasted? I will test it in my Power BI Desktop.

Regards,
Lydia

[astautz]

Sure!

The data table looks like this:

As you can see, the Invoice # on the left is shared across all the product rows that are on the invoice. So the Average Invoice Value measure needs to pick all those up, sum their Gross Profit (GP), and then average those summed GPs across all the Invoice #s. I think the [Avg Invoice Value] measure I described in my first post accomplishes that.

It gives me good results: the Avg Invoice Value for each week, for example:

The problems start when I try to get a cumulative sum going on a measure that is based on the [Avg  Invoice Value].

My current cumulative measure is defined in my first post. It gives the following result, which is fairly close to correct, but still wrong:

The black line is correct. It is multiplying the result of [Avg Invoice Value] for each week against that week's customers. But the cumulative sum only works for the first few weeks, then goes haywire.

So week 29, $4M that week, $4M cumulative. Week 30, $1M that week, $5M cumulative. Looks good. But then at week 35: $1M that week, plus $21M through Week 34, ought to be a cumulative $22M, and instead the cumulative line decreases, and the total it gives is still $21M.

What's going on?

My latest theory is that 'Calendar' filters in the FILTER() portion of the Cumulative Total measure are overwriting the 'Calendar' filters that make [Avg Invoice Value] work like it's supposed to. I can use VAR - RETURN syntax in the Cumulative measure to force [Avg Invoice Value] to keep its original filters, but then I lose the week-by-week responsiveness of that function...

Maybe a KEEPFILTERS somewhere would help?

Any ideas are welcome! Thanks for the help so far. 

[astautz]

Thanks for the suggestion!

I tried doing this:

[? Avg Invoice Value] :=

                                      AVERAGEX(
                                                        CALCULATETABLE(

                                                                                     SUMMARIZE ( 'Data',
                                                                                                             'Data'[Invoice #]
                                                                                                           ),
                                                                                     ALL('Calendar')
                                                                                     ),
                                                       CALCULATE ( SUM ( 'Data'[Gross Profit] ), ALL('Products')
                                                                           )
                                                         )

That is, I wrapped the SUMMARIZE in CALCULATETABLE so that I could an ALL( 'Calendar'). It works in the sense that it fixes the decreasing cumulative measure problem, but it only does that because it's returning a single value for all dates: the all-time average invoice value.

Is there something else I can try to preserve the date-responsiveness of average invoice value? I'd like the Cumulative Measure to be able to use an Average Invoice Value that varies over time with the underlying data.

[Greg_Deckler]

Maybe use ALLEXCEPT instead of ALL to preserve your date context?

[v-yuezhe-msft]

@astautz,

Could you please share sample data of your tables and post expected result here?

Regards,
Lydia

[Greg_Deckler]

Try using an ALL() in your SUMMARIZE in your Avg Invoice Value measure.