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 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],
			ALLSELECTED(SingleServers[Dept])
		)
	)

[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 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],
			ALLSELECTED(SingleServers[Dept])
		)
	)

[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

[v-sihou-msft]

@dedelman_clng

In this scenario, since you separate the server detail into two tables, you can't use a single measure to dynamically retrieve the data from two tables and calculate the percentage. The better practice is adding a column in server detail table for tagging single or shared, then you can use ALLEXCEPT() to group your calculation based on this tagging column.

Regards,

[dedelman_clng · Accepted solution]

Hi @v-sihou-msft

Somehow I made 2 or 3 copies of this thread, but the main one is here:

https://community.powerbi.com/t5/Desktop/Allocating-costs-from-one-table-to-values-in-another-table/...

I will be sharing PBIX files with both split and non-split tables showing that both methods can work.

Thanks,

David