Average of Averages by Row in Pivot Table

[ollieotis]

Hello,

First off, the official disclaimer... I'm new to power pivot, but just finished reading Rob and Avi's book, and have spent the last few weeks learning as much as I can. Apologies if I'm using incorrect terminology or am asking an obvious question, but the solution to the problem that led me to power pivot in the first place continues to evade me. I've made what I feel to be a lot of headway in other areas but this one is killing me, and no amount of forum searching has lead me to an elegant solution. I think I know why this isn't simple, but not sure I can explain it succinctly. Side note: power pivot is AMAZING and I keep finding myself asking where it has been all my life!

The problem:

I'm calculating Days Sales Outstanding (DSO) per month, or AR Balance / Average Daily Sales. I need a dynamic table, where I can modify the filter context numerous times based on need, e.g. by customer, region, state, etc., not just by a single column or filter. From there, I need the monthly average of the results generated by the measures. The table below outlines what I'm looking for, with the Jan-Mar DSO results calculated as:

DIVIDE(sumx('DSO_Base_Data','DSO_Base_Data'[201701 _AR Balance]),(SUMX('DSO_Base_Data','DSO_Base_Data'[201701 Invoice])/92))

Where my source data may be 10-15 lines per customer ID, or per person responsible, etc.

The average column below is just a simple Average() formula bolted on the side for illustrative purposes, but highlights ultimately what I'm trying to get into my pivot table.

[TABLE="width: 394"]
<tbody>[TR]
[TD]CUSTOMERID[/TD]
[TD]Jan DSO[/TD]
[TD]Feb DSO[/TD]
[TD]Mar DSO[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]CID000501[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD]CID000636[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]CID000969[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]281[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD]CID001159[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]165[/TD]
[/TR]
[TR]
[TD]CID001623[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD]CID002271[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]216[/TD]
[TD="align: right"]173[/TD]
[/TR]
[TR]
[TD]CID003183[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]185[/TD]
[/TR]
[TR]
[TD]CID003222[/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]CID008639[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]CID012800[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]CID017029[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]CID218504[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]76[/TD]
[/TR]
</tbody>[/TABLE]

Some of the research I've done leads me to potential use of the summarize() function, but I don't seem to have that in my list of formulas (using 2010).

Any ideas how I might approach this one?

Many thanks in advance for any help provided!

 

[Matt Allington]

Have a read of my article here https://exceleratorbi.com.au/use-sum-vs-sumx/

 

[ollieotis]

Thank you, Matt. I'm probably missing something obvious here, but I used sumx to ensure I got the correct grand total at the bottom of the pivot table, and did an offline check to confirm that calculation came out correctly. After reading your article though (which was a good refresher from the book I just finished), I'm thinking sum would have worked just as well in this case. The row and filter context concepts, and difference between aggregator and iterator functions are starting to become clearer to me, but I'm not seeing how that will help me here.

The grand total is correct, but what I'm looking for is a fourth column that calculates the average of that row's measure results. The following (from a smaller file used to test) seems to work, but I need to account for periods where there are no values:

divide('DSO_Base_Data'[Jan DSO]+'DSO_Base_Data'[Feb DSO],2)

Maybe I'm overthinking this? Just need a way to make the denominator account for months where there are no values and the above would seem to work.

Thanks!

 

[ollieotis]

I ended up solving the issue, although I would say my solution doesn't feel very elegant:

First, to account for periods with no values in my denominator, I used the following measure to return a value of 1 or blank:

IF((calculate(countx('DSO_Base_Data',DSO_Base_Data[Jan DSO]),FILTER('DSO_Base_Data',countx('DSO_Base_Data',DSO_Base_Data[Jan DSO])<>Blank())))=Blank(),Blank(),1)

After replicating the measure for the other two months, I then used a simple divide function:

divide('DSO_Base_Data'[Jan DSO]+'DSO_Base_Data'[Feb DSO]+'DSO_Base_Data'[Mar DSO],'DSO_Base_Data'[Count Jan]+'DSO_Base_Data'[Count Feb]+'DSO_Base_Data'[Count Mar])

This seems to work no matter the filter context applied in the pivot table. Just feels really clunky, and will get painful once I build out a 12-24 month view, and have to keep updating manually for trailing views.

 

[gazpage]

Sorry, but I can't really follow your descriptions. Are you using daxformatter.com rules of formatting? ie a column is prefixed by the table name, but a measure is not? I suspect you are not, but can't tell. If you can explain the structure of the underlying data, either by giving the column names, or a screenshot I'm sure someone can help you.

Apologies if this is what you've done, but you should be trying to create a single measure that calculates all of the cells in the table. You would then want to drag CUSTOMERID into the rows, and MONTHS into the columns. Let's assume that you've managed to write the measure that calculates the values for each cell in the table and called it [DSOMeasure], then to get your average column on the end it would be something like.

=AVERAGEX(
    VALUES ( Dates[Months] ),
    [DSOMeasure]
)

AVERAGEX is an iterator and will iterate through every month returned by VALUES ( Dates[Month] ). VALUES() operates in the existing filter context, so in a column for a given month it will iterate for one month only and return the average, which is just [DSOMeasure]. In the total column, there is no month filter context, so it will do the average of [DSOMeasure] for each month, ie the desired result. NB this only works because [DSOMeasure] is a measure and so context transition is carried out, if you replaced that with just DAX code you would need to wrap it in a CALCULATE() to get the same effect.

 

[ollieotis]

Thank you, Gazpage. Let me see if I can explain this a little better.

My output for this test case at this point looks like the following (will be part of a much more complex table with other metrics later on, where month column headers won't work):

[TABLE="width: 585"]
<tbody>[TR]
[TD]CUSTOMERID[/TD]
[TD]Jan DSO[/TD]
[TD]Feb DSO[/TD]
[TD]Mar DSO[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]CID001025[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]CID001405[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]CID002775[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]CID002843[/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]CID002988[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]CID005482[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]CID006467[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]CID007680[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]57[/TD]
[/TR]
</tbody>[/TABLE]

My data table is structured as follows:

[TABLE="width: 598"]
<tbody>[TR]
[TD]Project[/TD]
[TD]201701 _AR Balance[/TD]
[TD]201702 _AR Balance[/TD]
[TD]201703 _AR Balance[/TD]
[TD]201701 Invoice[/TD]
[TD]201702 Invoice[/TD]
[TD]201703 Invoice[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] (140,730)[/TD]
[TD] (141,255)[/TD]
[TD] 1,575[/TD]
[TD] (99,636)[/TD]
[TD] (82,089)[/TD]
[TD] (115,294)[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] (113,203)[/TD]
[TD] (52,052)[/TD]
[TD] 8,243[/TD]
[TD] 198,966[/TD]
[TD] 197,104[/TD]
[TD] 193,052[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] (113,152)[/TD]
[TD] (121,629)[/TD]
[TD] (162,015)[/TD]
[TD] (34,384)[/TD]
[TD] (85,620)[/TD]
[TD] (123,159)[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] -[/TD]
[TD] 2,385[/TD]
[TD] 2,590[/TD]
[TD] -[/TD]
[TD] 2,385[/TD]
[TD] 2,590[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD] 4,308,349[/TD]
[TD] 3,441,112[/TD]
[TD] 3,656,067[/TD]
[TD] 6,398,352[/TD]
[TD] 6,240,404[/TD]
[TD] 5,563,036[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD] 5,240,348[/TD]
[TD] 5,139,773[/TD]
[TD] 5,560,221[/TD]
[TD] 4,507,568[/TD]
[TD] 4,878,939[/TD]
[TD] 4,912,535[/TD]
[/TR]
</tbody>[/TABLE]


The first measure I wrote to get DSO for each month is:

Jan DSO = divide(sumx('DSO_Base_Data','DSO_Base_Data'[201701 _AR Balance]),(SUMX('DSO_Base_Data','DSO_Base_Data'[201701 Invoice])/92))

where 92 is number of days in 3 month period and invoice value is sum of invoices for those 3 months. repeat for Feb & Mar

The second measure I wrote provides me with a count of 1 or blank() per month (deleted table references before measures - not quite sure how to do that when writing the formulas out, and daxformatter.com returned same code):

Jan Count = IF((calculate(countx('DSO_Base_Data',[Jan DSO]),FILTER('DSO_Base_Data',countx('DSO_Base_Data',[Jan DSO])<>Blank())))=Blank(),Blank(),1)

repeat for Feb & Mar

The average measure is a function of the sum of the count values and sum of the DSO values (again, deleted table references from measures):

Jan - Mar Average DSO = divide([Jan DSO]+[Feb DSO]+[Mar DSO],[Count Jan]+[Count Feb]+[Count Mar])

I tried replicating this for a 12 month period last night, and it crashed on me and I lost a bunch of work. No big deal, but I'm trying to incorporate this analysis into a more complex table that incorporates data from several other data sources, where months aren't applicable in the column header. Based on what you said, I'm thinking I can restructure my data table to include months in a column, but that won't let me solve for my end table that I'm looking for. At least I don't think it will.

As always, thank you so much for helping me with my problem! Hopefully this is a little easier to understand now.

 

[gazpage]

Yes. I can now see that your data is not well structured for use in Power Pivot.

You want the data to have 4 columns:

Project
Month
Item
Value

Where Item is either AR Balance or Invoice. Use Power Query to do that, transpose your table and then unpivot the A to F columns that you will end up with.

 

[gazpage]

https://expirebox.com/download/3e5fb5a77ce0956d229dae636c90fa73.html

I spent the 5 mins it took to make an example. You should be able to see the query steps I did to unpivot your data in Edit Queries, and follow the Average logic in the measures. I realise DSO is not calculated correctly, but the logic should hold.

Gary

 

[ollieotis]

Awesome, thanks Gary! I'm not sure I've fully grasped it all yet, but the point on data structure makes perfect sense and you've given me what I need to figure it out from here. I can't download the file, but I can easily modify my initial query to fit this format (and will remember to set all of them up like this moving forward).

Again, thank you!