Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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" } ) ) ) ) )
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" } ) ) ) ) )
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".
That isn't the case. Just go for it.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |