cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculate OCPH per account category

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

1 ACCEPTED SOLUTION
Community Support

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

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

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

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.