Allocating costs from one table to values in another table

[dedelman_clng]

I'm hoping someone can point me in the right direction, because I'm starting to think what I'm trying to do can't be done.

Data Model:

Server Detail (InstanceID) 1-to-many to BillingDetail (InstanceID, ExtendedCost)

Standard Date table

I split ServerDetail into SingleServers and SharedServers based on certain criteria (there is no overlap between SingleServers and SharedServers, and every row from ServerDetail goes into one or the other).

So the data model really looks like this:

I need to total up the costs, per month and department, of the SingleServers, and figure out the percentage of each for a given month:

Total Cost := CALCULATE(
	sum(BillingDetail[ExtendedCost]),
		 'SingleServers'
	)

%GT := DIVIDE(
		[Total Cost],
		CALCULATE(
			[Total Cost],
			ALL(SingleServers[Dept])
		)
	)

So for example I would get

So far so good.  Now I need to compute the total cost for Shared Server per month:

Total Shared Cost := CALCULATE(
	sum(BillingDetail[ExtendedCost]),
		'SharedServers'
)

The final calculation I need to do (and hence, display) is the total cost of each single server (per month/dept) including that department's allocation (%GT) of the shared servers.  It seems like it should be a simple measure like

 Total With Alloc := [Total Cost] + ( [%GT] * [Total Shared Cost] )

But this is just showing me the value of [Total Cost]. I've tried wrapping it in CALCULATE, setting variables, everything I can think of - I can't seem to get the "Total Shared Cost" value available on the "SingleServer" side.

Am I even attempting something that is possible?

Thanks for any and all help

David Edelman

[dedelman_clng · Accepted solution]

OK, I've gotten really, really close, but while this new measure is accurate for month/department, it is inaccurate for a year/department.  The grand total over all departments for a year is correct, as well as the overall grand total.  It is just the year totals for a department that are wrong.  Here is the new measure:

TotalWithShared := 
	var TSC = CALCULATE(
			[Total Shared Cost],
			ALLSELECTED('SingleServers')
		)
return [Total Cost] + ( [%GT] * TSC )

What is really odd is that some of the year totals for a department are low, while others are high, completely balancing each other out at the grand total level.  Am I looking at an order of operations scenario or something else bizzare?

Thanks,

David

[dedelman_clng · Accepted solution]

Hi @dkay84_PowerBI - I'm still working on anonymizing the data, but I have discovered the "discrepancy":

The source data I am looking at (to verify that the model works) is an Excel sheet where the yearly totals are a sum of the monthly totals (i.e. Jan + Feb + Mar + ...).  PowerBI/DAX calculates the year level separately, not as a sum of the months.  I have written the formulae in Excel to mimic this behavior and now PowerBI matches.

So I need to go back to my business users and determine which kind of yearly/grand total they want to see (if at all).  I will update this post when I know more.

David

[dedelman_clng · Accepted solution]

OK, I've gotten really, really close, but while this new measure is accurate for month/department, it is inaccurate for a year/department.  The grand total over all departments for a year is correct, as well as the overall grand total.  It is just the year totals for a department that are wrong.  Here is the new measure:

TotalWithShared := 
	var TSC = CALCULATE(
			[Total Shared Cost],
			ALLSELECTED('SingleServers')
		)
return [Total Cost] + ( [%GT] * TSC )

What is really odd is that some of the year totals for a department are low, while others are high, completely balancing each other out at the grand total level.  Am I looking at an order of operations scenario or something else bizzare?

Thanks,

David

[LaurentCouartou]

Due to the way you calculate your share of total cost, your measure is non additive. (Can you see why?)

I would recommend adding the shared cost at the billing details level (as a calculated column) and then just do a simple SUM on that column. 

[dedelman_clng · Accepted solution]

"What is really odd is that some of the year totals for a department are low, while others are high, completely balancing each other out at the grand total level.  Am I looking at an order of operations scenario or something else bizzare?"

I determined the issue here - the data I was comparing it to in Excel was a subtotal of the months, while DAX/PowerBI does the actual calculation of %GT * Total Shared Cost at the year level.  The users don't care about a yearly total, so there's no further issue there.

As mentioned in this thread and others, here are links to a sample of the report with split tables and without:

Billing with Split Server tables

Billing with single Server table

Thanks to everyone who contributed to the thread(s). Please let me know if there are any other questions.

David

[dkay84_PowerBI]

Can you share some sample data?  It's very hard to troubleshoot DAX without having data to play with.

[dedelman_clng]

@dkay84_PowerBI - I will work on anonymizing the data and send you a link to OneDrive. Thanks.

[dedelman_clng · Accepted solution]

Hi @dkay84_PowerBI - I'm still working on anonymizing the data, but I have discovered the "discrepancy":

The source data I am looking at (to verify that the model works) is an Excel sheet where the yearly totals are a sum of the monthly totals (i.e. Jan + Feb + Mar + ...).  PowerBI/DAX calculates the year level separately, not as a sum of the months.  I have written the formulae in Excel to mimic this behavior and now PowerBI matches.

So I need to go back to my business users and determine which kind of yearly/grand total they want to see (if at all).  I will update this post when I know more.

David

[dedelman_clng]

I'm hoping someone can point me in the right direction, because I'm starting to think what I'm trying to do can't be done.

Data Model:

Server Detail (InstanceID) 1-to-many to BillingDetail (InstanceID, ExtendedCost)

Standard Date table

I split ServerDetail into SingleServers and SharedServers based on certain criteria (there is no overlap between SingleServers and SharedServers, and every row from ServerDetail goes into one or the other).

So the data model really looks like this:

I need to total up the costs, per month and department, of the SingleServers, and figure out the percentage of each for a given month:

Total Cost := CALCULATE(
	sum(BillingDetail[ExtendedCost]),
		 'SingleServers'
	)

%GT := DIVIDE(
		[Total Cost],
		CALCULATE(
			[Total Cost],
			ALL(SingleServers[Dept])
		)
	)

So for example I would get

So far so good.  Now I need to compute the total cost for Shared Server per month:

Total Shared Cost := CALCULATE(
	sum(BillingDetail[ExtendedCost]),
		SharedServers
)