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
Twilla
Helper I
Helper I

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

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_IDCategoryAmount
20150101A5
20150103B6
20150103C3
20150109A9
20150110B6
20150111C4

 

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-WeekWeekCategoryAmount 
2015W01A3,645-3*(5/11)
  B4,366-3*(6/11)
 W02A6,69-4*(9/15)
  B4,46-4*(6/15)
Total  19,003,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

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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" } )
                    )
            )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@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" } )
                    )
            )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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".

 

@Twilla,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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.