Forum Discussion
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
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
2 Replies
v-lili6-msftCommunity 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
Cheers v-lili6-msft, works like a charm. Much appreciated!