DAX formula not totaling correctly

[GilesWalker]

Hi everyone,

I am having trouble with a DAX formula not summing properly. It is due to me not filtering correctly however after many days of trying different variations I cant seem to get it to work. Here is the data and formula:

This data is coming from two tables:

1. Concentrates

2. DateKey

The concentrates table contains the week and all the revenue columns. The DateKey contains the Financial month column. The two tables are joined with a One-Many both ways relationship on a column I made which contains the financial year (2016) and the weeks (1-53) combined, this column is in both tables.

What I am trying to acheive is to work out what the revenue/budget is for the month. The issue is we only measure by week, and some weeks will cross over months. An example is week five in the above picture. What I have done to combat this is create a measure which calculates the number of days in a week relative to the month:

Countrows YearWeek = CALCULATE(SUM(DateKey[Index 1]), DISTINCT(DateKey[Financial YearWeek]))

The DateKey[Index 1] is just a column containg a value of 1 for every row.

I then added this measure in the concentrates table as a column:

Days in week = [Countrows YearWeek]

Next I created the following measure to calculate the Daily budget:

Daily budget revenue = DIVIDE(SUM(Concentrates[Budget $ concentrates]),[Countrows YearWeek])

This was then added into the concentrates table as a column:

Daily budget revenue concentrates = [Daily budget revenue]

I then created this measure to give me the daily budgeted revenue * the days in the week dependant on the month:

Monthly budget concentrates = [Daily budget rev concentrates]*[Countrows YearWeek]

As you can see from the picture above each it works, however the total is showing too much, it should be roughly 4 million not 224 million. I beleive the reason is because I am missing something in the filtering. I have have tried adding in ALLEXCEPT for the month or the week and it still results in the same total figure.

Hope someone can assist.

Thanks,

Giles

[Sean]

@GilesWalker Can you post sample data so we can try to create the same table visual as the picture you posted?

[GilesWalker]

@Sean - here you go:

Week	Countrows YearWeek	Revenue $ actual	Budget $ concentrates	Daily revenue	Daily budget revenue	Daily budget revenue concentrates	Financial month	Monthly budget concentrates
	5						Jun
1	4	63899	27867	15974.75	6966.75	$6,966.75	Jul	27867
2	7	84741	48766	12105.85714	6966.571429	$6,966.57	Jul	48766
3	7	84078	48766	12011.14286	6966.571429	$6,966.57	Jul	48766
4	7	77391	48766	11055.85714	6966.571429	$6,966.57	Jul	48766
5	6	99303	48766	16550.5	8127.666667	$6,966.57	Jul	41799.42857
5	1	99303	48766	99303	48766	$6,966.57	Aug	6966.571429
6	7	67333	48766	9619	6966.571429	$6,966.57	Aug	48766
7	7	86126	48766	12303.71429	6966.571429	$6,966.57	Aug	48766
8	7	-22938	48766	-3276.857143	6966.571429	$6,966.57	Aug	48766
9	7	135451	48766	19350.14286	6966.571429	$6,966.57	Aug	48766
10	2	105555	85922	52777.5	42961	$12,274.57	Aug	24549.14286
10	5	105555	85922	21111	17184.4	$12,274.57	Sep	61372.85714

[Sean · Accepted solution]

Have you seen this post...

http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

I'm not quite clear how your orginal 'concentartes' table is set up?

[GilesWalker]

@Sean - Thanks for your help with this. The link you sent through explained my issues perfectly and with a little tweaking I got it to work:

Monthly budget concentrates =

         IF(COUNTROWS(VALUES(Concentrates[Financial YearWeek]))=1,

                  [Daily budget rev concentrates]*[Countrows YearWeek],

                            SUMX(VALUES(Concentrates[Financial YearWeek]),

                                  [Daily budget rev concentrates]*[Countrows YearWeek]))

Thanks,

Giles