topic Calculate costs derived from another Department Category (operating vs corporate data) in DAX Commands and Tips

Calculate costs derived from another Department Category (operating vs corporate data)

gerardotrejogcg — Mon, 24 Jan 2022 05:07:01 GMT

I have a requirement to assign Corporate Expenses to the several Operating Departments according with the percentage of the Cost of Sales.

1. The percentage of Operating Cost of Sales per Department (% Operating Cost of Sales)

2. The Total Corporate Expenses (Corporate Amount)

3. The expected result is to multiply The Corporate Cost of Sales by the % of Expenses of each Operating Department.

The issue is that I'm not able to assing the Corporate cost on the Operating side.

First row = 22,207,639.31 * 0.26%

....

Last row = 22,207,639.31 * 3.13%

Re: Calculate costs derived from another Department Category (operating vs corporate data)

ValtteriN — Mon, 24 Jan 2022 06:18:28 GMT

Hi,

This kind of dax should do what you want:
[Allocated costs] = MAX(Table[% operating cost of sales])*CALCULATE(AVERAGE(Table[Corporate expenses]),ALL(Table), Table[Corporate expenses]<>BLANK())

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Calculate costs derived from another Department Category (operating vs corporate data)

gerardotrejogcg — Mon, 24 Jan 2022 18:32:56 GMT

I tried to adequate the provided dax with no luck.

This is the expected result I want to reach. Corporate Expense in gray means that currently I'm not able to assign that measure for "Operaciones" (Operating Department) since the result is calculated for "Corporativo" (Corporate Department) only.

The result is limited to apply the Corporate Expense multiplied by % Operating Cost of Sales.

This is the sample data link

Operating Cost of Sales =
CALCULATE (
CALCULATE (
'Measure Slicers'[Measure Selection P&L],
ALLEXCEPT ( 'Accounts P&L', 'Accounts P&L'[KPI Name] ),
ALLEXCEPT( 'Dimension Sets', 'Dimension Sets'[Deparment Category] ),
FILTER ( 'Accounts P&L', 'Accounts P&L'[KPI Code] = "T200" ) --> Cost of Sales
),
FILTER ( 'Dimension Sets', 'Dimension Sets'[Deparment Category] = "Operaciones")
)

% Operating Cost of Sales =

DIVIDE ( [Amount P&L MXN], [Cost of Sales Operaciones], BLANK() )

Corporate Expense =

CALCULATE (

CALCULATE(

[Actual Expense Amount],

ALLEXCEPT ( 'Dimension Sets', 'Dimension Sets'[Deparment Category] )

FILTER ( 'Dimension Sets', 'Dimension Sets'[Deparment Category] = "Corporativo")

)

Re: Calculate costs derived from another Department Category (operating vs corporate data)

v-yalanwu-msft — Thu, 27 Jan 2022 06:27:30 GMT

Hi, gerardotrejogcg ;

Try it.

Measure = MAXX(ALL('Table'),[Corporate Expense])*[% Operating Cost of Sales]

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.