Weekly report where some rows of fact table have to be subtracted from others.

[Twilla]

Hi all, 

I have the following issue: 

I have a fact table with 3 columns: Date_ID(yyyymmdd), Category and Amount. Date_ID is linked to my Calendar table which has a column Week and Year-of-Week

Date_ID	Category	Amount
20150101	A	5
20150103	B	6
20150103	C	3
20150109	A	9
20150110	B	6
20150111	C	4

What I want to create: a weekly report that shows amount of category A and B subtracted with C proportional to the amount of A and B. The idea is that I can drill/expand from year-of-week to week to category. I tried several measures but all of them got me an erroneous rowtotal when drilling. 

This is how it should look like (last column shows formula):

Year-of-Week	Week	Category	Amount
2015	W01	A	3,64	5-3*(5/11)
		B	4,36	6-3*(6/11)
	W02	A	6,6	9-4*(9/15)
		B	4,4	6-4*(6/15)
Total			19,00	3,64+4,36+6,6+4,4

 Anyone got an idea how to create this with a correct row total when drilling up/down? C should always be allocated to A and B proportional to the amount in a certain week. So if you drill up to 2015, row total should still be 19!

Cheers,

Twilla

[v-chuncz-msft · Accepted solution]

@Twilla,

Instead, you may use DAX below to create a calculated table.

Table =
SUMMARIZECOLUMNS (
    'Calendar'[Year-of-Week],
    'Calendar'[Week],
    'fact'[Category],
    "Amount", IF (
        MAX ( 'fact'[Category] ) IN { "A", "B" },
        SUM ( 'fact'[Amount] )
            * (
                1
                    - DIVIDE (
                        CALCULATE ( SUM ( 'fact'[Amount] ), 'fact'[Category] = "C" ),
                        CALCULATE ( SUM ( 'fact'[Amount] ), 'fact'[Category] IN { "A", "B" } )
                    )
            )
    )
)

[Twilla]

Hi,

Thanks for the help but I don't think it is correct...

this part of the formula will be blank:

CALCULATE ( SUM ( 'fact'[Amount] ), 'fact'[Category] = "C" )

This happens because you have the IF statement that doesn't (and shouldn't) include C

If I would use the "All" function in the filter, it will ignore the fact that I only want to have the C values of the correct "week".

[v-chuncz-msft]

@Twilla,

That isn't the case. Just go for it.

[Twilla]

Yeah, my bad

It is not a measure so my remark about the local context is not correct. The proposed solution works fine.

Cheers,

Tom