Totals not adding up properly (but using multipe tables)

DAX DAX Calculations
1

Hi,

I’m creating a forecast for the sales in a month. It looks at what the average sales per day are for the month. Then multiples that by the number of days left for that day of the week per month. Like so

image
image.png808×244 10.1 KB

The problem is that the total’s aren’t working properly. Because instead of summing the Total Forecast, it’s multiplying the total of the weekly forecast by the number of days left.

I know normally you can get around this with SumX. But I’ve got two tables involved here. One the date table (where I can the number of days left in the month etc) and my fact table.

Any ideas?

2

Can you upload some data along with what measures you are using?

3

Sure, here is a example data set that illustrates the problem

Forecast example.pbix (151.8 KB)

Here you can see that the visual is saying the total for “Total Forecast for Month” is $1,014,000. But if you sum the details you get $1,001,667

image
image.png904×310 10.5 KB

image
image.png946×244 7.76 KB

Thanks,

4

@MathewA
If you change your Total Forecast to Month measure to this:

    Total Forecast for Month = 
    SUMX( 
    VALUES ( Dates[DayOfWeekName]), 
    CALCULATE(
        [Total Sales] + [Weekly Forecast])
    )

Then the totals should be what you expect.

SUMX%20total%20Final%20Table
SUMX%20total%20Final%20Table710×241 10.7 KB

Not sure on the other measures if those total are what you would expect, but you can repeat the above process for each measure.

Hope it helps

Nick

Enterprise%20DNA%20Expert%20-%20Small

1 Like
5

Worked perfectly thanks so much!