Need to AVERAGE when Rolling up to a Month or Quarter

[Roseventura]

I have an inventory report that gets refreshed ONCE a WEEK.  This once/week date is my RUN DATE.  The visual below shows the trend by Run Date. 

If you look at the visual by Run Date, it displays what our total inventory level was on that Run Date.  This is correct.  However, my users want to view or “roll up” by Month and by Quarter.  I can’t roll up the visual by month or quarter because then it will ADD the inventory $$ for each week, which is incorrect.  See below.

My customer would be satisfied with the AVERAGE for the month or the quarter.

I tried AVERAGE and AVERAGEX, but those measures give me an average for the Run Date.  For example, if there are 400 lines that make up the total of $82,805,754 for 1/7/2019, AVERAGE or AVERAGEX gives me the average of all those lines.  I need it to TOTAL each Run Date, but then AVERAGE the totals when looking at it by month or quarter.

I tried creating a summary file from the raw data, but I can’t do that because I need the inventory levels broken down by Warehouse, hence the reason for multiple lines for one Run Date.

Here's an Excel representation of what it's doing wrong and what I need:

Any help with be greatly appreciated!

[OwenAuger · Accepted solution]

Hi @Roseventura 

You can control the granularity of the figures that are averaged by providing an appropriate table as the first argument of AVERAGEX.

In your case, a measure like this should do the trick:

Inventory $$ Average by Run Date =
AVERAGEX (
    VALUES ( YourTable[Run Date] ),
    CALCULATE ( SUM ( YourTable[Inventory $$] ) )
)

This measure will iterate over the distinct Run Dates, and sum Inventory $$ for each.

Then it will average the resulting sums.

Does this work for you?

Regards,

Owen

[Roseventura]

This is exactly what I needed!  Thank you!

Rose

[MFelix]

Hi @Roseventura ,

Altough I agree with the solution provided by @OwenAuger , believe that your issue is to get the lates value for the month / quarter / year instead of calculating the average for the month. Basically you want to get the 28/01/2019 values for month results and the 30/03/2019 for the quarter correct?

I would do the following:

• Create a calendar table
• Make a relationship with the inventory table based on Run Date
• Add the following measure:

Inventory Total =
CALCULATE (
    SUM ( Table1[Quantity] );
    FILTER ( 'Calendar'; 'Calendar'[Date] = MAX ( Table1[Run Date] ) )
)

Now add a hierarchy of the calendar date to your visual and users can drill down and up always pickin up the latest value.

On the example below I have 3 products per date and for each date the values change by 10 so in first date total inventory is 30 and on the last date (May) is 90 as you can see the values match on month end results.

Thje trick over here is to place the calendar as a date hierarchy and remove the day from hierarchy and then add again calendar date but as date only and you will get correct result.

Check attach PBIX file.

Regards,

MFelix