topic Re: Estimated total sales based on the mean in DAX Commands and Tips

Estimated total sales based on the mean

Jan_Trummel — Tue, 31 Jan 2023 16:25:16 GMT

Hello community,

I have a longer question. I will try to describe the whole thing as clearly as possible.

1) This is my data

I use the database ContosoRetailDW_2019. From this I imported the following 2 tables:

Fact Sales
DimDate

Of course, both tables are linked via a 1-to-N relationship.

I only imported sales before 8/1/2009. If I have the total sales per year and month displayed in a matrix, this is the picture I get:

2) This what I want to do

I would now like to estimate the total sales for the months of August to December 2009, using the mean value of the total sales for the past few months. This is a bit difficult to describe, so I'll show you what I want to do in Excel:

3) These are my measures

3.1) The measure Total Sales

I start by creating the Total Sales measure:

Total Sales = SUM(FactSales[SalesAmount])

3.2) The measure Total sales one month all years

Now I need a measure that adds up the total sales of a month over all years. I will use it as a dividend in my closing calculation. For a better understanding, I'll show you again in Excel what I want to calculate:

Here is the measure Total sales one month all years:

Total sales one month all years =

CALCULATE(

    [Total Sales],

    ALL(DimDate[CalendarYear])

)

When I drag the measure into the matrix I get this picture:

3.3) The measure Number of total sales one month all years (here are the problems!)

Now I need a measure that gives me the number of total sales. So that you know what I mean, I'll show you again in Excel what I want to do:

I wrote the following measure Number of total sales one month all years:

Number of total sales one month all years = 

COUNTX(

    ADDCOLUMNS(

        SUMMARIZE(

            ALL(DimDate),

            DimDate[CalendarYear],

            DimDate[CalendarMonthLabel]

        ),

        "Result",

        [Total Sales]

    ),

    [Result]

)

The idea is this: I create a table grouped by year and month with SUMMARIZE and then add a result column with ADDCOLUMNS, in which I calculate the total sales. I then use COUNTX to count how many results I have in my summary table.

You can see that I get the correct numbers in the values of the matrix. The result column is also correct. However, I cannot explain the values in the result line. Here the total amount (31) comes out everywhere.

3.4) The measure Estimated total sales

Anyway, I'll keep going on. I now create the Estimated total sales measure:

Estimated total sales =

[Total Sales One month All years] / [Number of Total Sales One Month All Years]

3.5) The measure Total sales or estimated sales

Now I create the final measure, which outputs either Total sales or Estimated total sales:

Total Sales or estimated total sales = 

IF(

    ISBLANK([Total Sales]), 

    [Estimated total sales], 

    [Total Sales]

)

I drag it into the matrix and of course the overall results are wrong:

It should actually look like this:

4) How are you doing right?

Thank you so much for reading this really long text!

I am of course aware that the problem lies in the measure Number of Total Sales One Month All Years. Unfortunately I can't find the right solution.

Can you help me?

Thanks in advance!

Re: Estimated total sales based on the mean

OwenAuger — Tue, 31 Jan 2023 22:09:57 GMT

Hi Jan_Trummel

Thanks for the great question, and laying out clearly everything you have tried so far!

My main recommendation is to use AVERAGEX to determine the estimated total sales average across all years.

Since AVERAGEX automatically excludes blanks, you can avoid writing any DAX to count nonblank values yourself.

Here is how I would write the Total Sales or estimated total sales measure. I have put all the logic in one measure, but you can certainly split the calculations across measures as you originally did.

Total Sales or estimated total sales = SUMX ( -- Sum by Year/Month SUMMARIZE ( DimDate, DimDate[CalendarYear], DimDate[CalendarMonthLabel] ), VAR TotalSales = [Total Sales] -- Average of Total Sales across all years VAR EstimatedTotalSales = AVERAGEX ( ALL ( DimDate[CalendarYear] ), [Total Sales] -- blanks automatically excluded from average ) RETURN -- Syntax sugar for IF ( ISBLANK ( TotalSales ), EstimatedTotalSales, TotalSales ) COALESCE ( TotalSales, EstimatedTotalSales ) )

Does this give the expected result?

Regards,

Re: Estimated total sales based on the mean

Jan_Trummel — Wed, 01 Feb 2023 09:21:50 GMT

Hello OwenAuger ,

thank you so much for your detailed answer! Using AVERAGEX and COALESCE definitely makes things a lot easier!

So your answer definitely helps me. Unfortunately, not everything is as I would like it to be. When I use your measure in the matrix I get this result:

The values in the Totals column are now correct. However, the annual result for 2009 and the overall result for the whole table are still wrong. I have circled the incorrect results in red, the correct results in green.

In the Excel spreadsheet I show you what the correct results should be. You only have to pay attention to the red colored cells:

The reason for the erroneous results must lie in the filter context, which is different for the overall results than for the values in the matrix.

Do you have any idea how I can get the right numbers in the overall results?

Re: Estimated total sales based on the mean

OwenAuger — Wed, 01 Feb 2023 12:08:40 GMT

Hi again Jan_Trummel

Thanks for testing it out.

That's odd, because in my test PBIX using I believe the same Contoso source data, I do get the correct results.

Link to my PBIX

In creating my dataset, I added a column 'Date'[CalendarMonth Number] which I set as a sort-by column for 'Date'[CalendarMonthLabel].

Here are the results I get, with the right-hand matrix being similar to yours, but with the correct values:

To be honest, I can't see any obvious reason why those totals could be wrong. Assuming we're using the exact same DAX expression for the measure, it must be a difference in the data model, possibly sort-by columns or relationships?

Could you compare the data model in my PBIX linked above to yours, and see if there's an obvious difference that might explain the results you're seeing? Or if you can share a link to your PBIX, I can take a look as well.

Regards

Re: Estimated total sales based on the mean

Jan_Trummel — Thu, 02 Feb 2023 07:02:09 GMT

Hello OwenAuger ,

thank you so much! I made a mistake when transferring your measures into my file:

I inserted my measure [Estimated total sales] into the COALESCE fuction instead of the variable EstimatedTotalSales!

Thank you again and have a good day!

Greetings

Re: Estimated total sales based on the mean

OwenAuger — Thu, 02 Feb 2023 11:48:22 GMT

Glad it's working - you're welcome! 🙂