Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jan_Trummel
Helper III
Helper III

Estimated total sales based on the mean

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:

Total sales per year and monthTotal sales per year and month

 

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:

This is what I want to do (shown in Excel)This is what I want to do (shown 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:

My measure should calculate the total sales for a month across all yearsMy measure should calculate the total sales for a month across all years

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:

My measure "Total Sales One month All years" in the matrixMy measure "Total Sales One month All years" in the matrix

 

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:

Now I need the number of total sales "in a line"Now I need the number of total sales "in a line"

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.

My measure "Number of total sales one month all years" generates incorrect values in the result line (framed in red)My measure "Number of total sales one month all years" generates incorrect values in the result line (framed in red)

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:

The overall results for the estimated values (outlined in red) are of course wrongThe overall results for the estimated values (outlined in red) are of course wrong

 

It should actually look like this:

You can see the correct overall results for the estimated values in red hereYou can see the correct overall results for the estimated values in red here

 

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!

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

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,

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

OwenAuger
Super User
Super User

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:

OwenAuger_0-1675252689831.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

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:

OwenAuger_0-1675252689831.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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,

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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 cells outlined in red still give an incorrect resultThe cells outlined in red still give an incorrect 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 cells colored red show the correct resultThe cells colored red show the correct result

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?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors