Finance: spread overhead via calculated percentage

[Anonymous]

Hi all,

Breaking my head on this one, need more DAX experience...

I've got

• a FACT table with an amount per date and per department
• a Date dimension (date, year)
• a Department dimension (Dept_code, Dept_name, Hierarcy (being Production and Overhead))

I would like to create a measure that

1) creates a table with percentages, being % of row amount value vs. total of the column, only for the production departments ( 'Dept'[Hierarchy] = "Production")
I've got a visual year filter, so that (I presume) it's important that this filter context still works with the measure

2) then, this table should be used to distribute an amount as per the percentages
This amount is the overhead amount, so CALCULATE ( SUM ( 'FACT'[Amount] , FILTER ( 'Dept' , 'Dept'[Hierarchy] = "Overhead" ) ) )
And then I'd like to show the sum of the production dept. + the spread amount as a total...

Now I need to go to old friend Excel to show what I mean:

Dept	Amount	%CT Amount	Spread	Total
A1000	8.384.135,06	89,68%	-                           218.971,73	8.165.163,33
A2000	1.882.990,41	20,14%	-                             49.175,85	1.833.814,56
A3000	245.492,76	2,63%	-                               6.421,67	239.071,09
A4000	-                       1.498.719,75	-16,03%	39.140,46	-                       1.459.579,29
A5000	335.051,44	3,58%	-                               8.741,29	326.310,15
TOTAL	9.348.949,92	100,00%	-                           244.170,08	9.104.779,84
overhead:	-                           244.170,08
TOTAL	9.104.779,84

I appreciate any help!!

[Anonymous]

I've gotten this far:

by using measure

%CT Measure =
DIVIDE (
    SUM ( 'FACT'[Amount] ),
    CALCULATE (
        SUM ( 'FACT'[Amount] ),
        ALLEXCEPT ( 'FACT', 'Date'[Year], Dept[Hierachy] )
    )
)

but I can't figure out how to exclude the Overhead department from my calculation...

[Anonymous · Accepted solution]

OK,

Filtering out Production:

%CT Measure = 
DIVIDE (
    SUM ( 'FACT'[Amount] ),
    CALCULATE (
        CALCULATE ( 
            SUM ( 'FACT'[Amount] ), 
            ALLEXCEPT ( 'FACT', 'Date'[Year], Dept[Hierachy] )
        ) ,
    FILTER ( Dept , Dept[Hierachy] = "Production" ) 
    )
)

correct placement helps 😉

Then, to have an amount to distribute, in Power Query I copied the FACT table and filtered the departement so that only Overhead Depts remained. From then on easy sailing...

If there's anyone who can show me a better way, I'd love to hear your solutions!