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
)

[SqlJason]

I didn't understand the issue well, but based on what I undersatnd, i would definitely combine both the single servers and shared servers into one table, with an extra column called Server Type (can have 2 values - Shared or Single). It is best to do it in Power Query, but you can make a calculated table also  - 

Server =
UNION (
    SUMMARIZE ( 'SharedServer', [InstanceID], [Dept], "ServerType", "Shared" ),
    SUMMARIZE ( 'ingleServer', [InstanceID], [Dept], "ServerType", "Single" )
)

Now you can use the same measures and then put ServerType on slicer/columns to see the measures for Single or Shared.

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

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

[dedelman_clng]

@SqlJason thank you, but that's not the issue I'm having.  I need to combine values from shared servers with values from single servers, calculating a percentage allocation of both.

[v-shex-msft]

Hi @dedelman_clng,

You can refer to below steps to achieve your requirement.

1. Use SELECTCOLUMNS funciton to choose the same part of each table, add a Tag column to remark the original table.

3. Merge these tables with UNION function.

4. Write two measures to calculate the percent of each table.

Sample:

Table formula:

SummaryTable=
Union(
SelectColumns(SingleServers,"InstanceId",[InstanceId],"Dept",[Dept],"ExtendedCost",Related(BillingDetail[ExtendedCost]),"Tag","SingleServers"),
SelectColumns(SharedServers,"InstanceId",[InstanceId],"Dept",[Dept],"ExtendedCost",Related(BillingDetail[ExtendedCost]),"Tag","SharedServers"))


Measures:

Total of SingleServers= Calculate(Sum(SummaryTable[ExtendedCost]),FILTER(ALL(SummaryTable),[Tag]="SingleServers"))

Total of SharedServers= Calculate(Sum(SummaryTable[ExtendedCost]),FILTER(ALL(SummaryTable),[Tag]="SharedServers"))

Percent of SingleServers=
DIVIDE(MAX(SummaryTable[ExtendedCost]),[Total of SingleServers],0)

Percent of SharedServers=
DIVIDE(MAX(SummaryTable[ExtendedCost]),[Total of SharedServers],0)

Regards,

Xiaoxin Sheng

[dedelman_clng · Accepted solution]

Hi @v-shex-msft, @SqlJason

I don't know how I ended up with 2 or 3 versions of this topic, but the main thread 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 they both work if you are interested.

David