Help with Calculations.

[IBIANALYST]

I have a peculiar problem, in my use case I am trying to calculate Manpower Cost as % Revenue, I have operations in 50 countries and earn revenues from 5 different business lines.

Now, Every Country has its its own revenue from different business lines, example

Country ‘A’ has revenue of  $100 from Business Line “AAA” and has manpower cost $20  for the same business Line “AAA”, So the Manpower Cost as % Revenue for business line “AAA” for Country ‘A’ comes to 20/100= 20%


Country ‘B’ has revenue of  $200 from Business Line “BBB” & has revenue of  $100 from Business Line “CCC” and has manpower cost $40  for the business Line “BBB” and $80  for “CCC” . So the Manpower Cost as % Revenue for business line “BBB” & “CCC” for Country ‘B’ comes to 20% for ‘BBB’ & 80% for ‘CCC’

But now, every country has Operations Support Manpower Expenses, and as per the methodology to calculate Manpower Cost as % Revenue for Operations Support we have to divide the whole revenue of the country with the Operation Support Expenses.

Now for Country A  has $10 as Operation Support Manpower Expenses and Country B has $15 as Operation Support Manpower Expenses. So the Manpower as a % of Revenue for Operation support for country A comes to $10/$100=10% and for Country B its $15/$300 = 5%

Here I hit the Snag, I am not able to fit all in one visual, I have to use 1 visual to show Manpower Cost as % revenue for 5 business lines because all 5 business lines has their own revenue and Manpower Expenses and I have to use a separate visual for OPS Support Manpower cost as % Revenue beacuse there is no revenue allocated aginst this so I have to calculate it aginst the whole Revenue of the operation.

I tried everything possible like putting all the dax calcualtion in to a calcualted table with help of add columns but nothing worked for me.

Can you guys help me finiding a way how can i fit it all in one visual.

[IBIANALYST · Accepted solution]

1. Putting % in column does not help as when you drag these columns in your visual as values it either sum, average, max, min, and etc them. you wont be able to take 1 single value that you want. There is no measure which support % value only way to get % with right value is to have base data and calculate it through them. 

2. Putting 2 measure or column as values in a visual will create that many column for each category. 

I found a temorary solution to add another line for revenue in my excel files. With Name Ops Support so now my visual is working.

[Anonymous]

So you would like to have a stacked column graph with the x axis categories being your product lines (e.g. AAA) and the y axis lines being manpower cost actual, manpower cost budget, ops support cost budget, ops support cost actual as percentages of revenue?

One way to do this would be to unify the x axis data range for ops support and manpower. That is to say that you would have a column of ops support as a % for AAA, BBB as well as manpower cost as a % for AAA, BBB. Then you could just put all four categories into the y axis of a stacked column graph and let the x axis be the unified categories.

Is this what you're looking to do?

[IBIANALYST]

Yeah that I dont want. i want OPS Support as a % revenue to be shown seperately as a business line. In the Image Just imagine  the two visual being one and OPS SUpport just being 6th Business Line.

[Anonymous]

Please send a mockup image of what you are trying to create.

[IBIANALYST]

Something like this.

[Anonymous]

I think you could achieve this with a new table that sources data from your old tables, with three columns: Product type (AAA,BBB,CCC), Ops support (%), and manpower (%).

Alternatively, if you can create a relationship between your tables where AAA,BBB,CCC are the keys (so a relationship between the columns that have that data) then you may be able to just drag and drop ops support and manpower into the data field.

If you are still struggling, please send some anonymised sample data.