Calculate OCPH per account category

[Anonymous]

Hi,

Im trying to calculate OCPH (overhead cost per hour) per account category:

My data is structured in the following way:

Cost object	Account	Amount	Qty
A	5000	200	4
A	5500	200	0
B	5000	500	5
B	5500	300	0
C	5000	50	0
C	5500	300	0
C	5000	200	0
A	9000	-500	-5
B	9000	-350	-4
C	9000	-400	-4

Account mapping table

Account	Account category
5000	Expense 1
5500	Expense 2
9000	Hours

My goal is to visualize my data in actual OCPH per account category (expense 1, 2, .....) per cost object

Cost object	5000	5500	Total cost	Hours	OCPH total	OCPH Expense 1	OCPH Expense 2	System OCPH
A	200	200	400	5	80	40	40	100
B	500	300	800	4	200	125	75	87,5
C	250	300	550	4	137,5	62,5	75	100

I have a measure for Total cost

Total cost = CALCULATE ( reporting value* ; 'account mapping' [account category] <> "Hours" )

And a measure for the Hours

Hours = CALCULATE (  quantity** ; 'account mapping' [account category] = "Hours" ) *-1

I have tried to use a simple DIVIDE formula,

OCPH per account category = DIVIDE ( Total cost ; Hours ; 0 )

 but that wont give me any data per cateogory because there is no DENOMINATOR per expense Account category, only in the "Hours" account category.

My report is ending up like this:

No filter on Cost object
Account category	Total cost	Total hours	OcPH
Expense 1	950
Expense 2	800
Hours		13
Total	1750	13	134,62

But my goal is to visualize the data like this:

No filter on Cost object
Account category	Total cost	Total hours	OcPH
Expense 1	950		73,08
Expense 2	800		61,54
Hours		13
Total	1750	13	134,62

Any hints on what I need to do to make this work? All help is highly appreciated.

* This is a measure summarizing amounts on all accounts

** This is a measure summarizing Qty on all accounts

[v-lili6-msft · Accepted solution]

Hi@ wes15

After my research , you can use these three measure like below:

Hours = CALCULATE (  SUM(Table2[Qty]) , FILTER(Table3,Table3[Account category] = "Hours" ) )*-1

Total cost = CALCULATE ( SUM(Table2[Amount]),  FILTER(Table3,Table3[Account category] <> "Hours" ))

OCPH per account category = VAR F=CALCULATE (  SUM(Table2[Qty]) , FILTER(ALL(Table3),Table3[Account category] = "Hours" ) )*-1 RETURN 
CALCULATE(DIVIDE([Total cost],F))

Result:

Here is Demo , please try it

https://www.dropbox.com/s/wihqdyqmf97431q/Calculate%20OCPH%20per%20account%20category.pbix?dl=0

Best Regards,

Lin

[Anonymous]

Cheers @v-lili6-msft, works like a charm. Much appreciated!