Need help: How to calculate the difference between a measure and a column

[EmeraldLake]

I've been pounding my head for days over this and also searched online a ton but didn't find a solution to the problem.  

Context/Background of the scenario, simplified for discussion of the Power BI problem: 

- A "pfCluster" can support many customer environments/buildouts. 

- For every customer environment, we need to assign 1 server to manage that customer environment.  And in some pfClusters, we have enough server reserve to manage the customer envirionments, but in others we don't have enough server reserve.  I need to find the "capcaity gap", so to speak

I have a table in Power BI as below, with only columns at the moment - no measure.   

pfCluster	Customer Environment	Server Capacity Reserve in the pfCluster
pfCluster_A	Env1	5
pfCluster_A	Env6	5
pfCluster_A	Env3	5
pfCluster_B	Env2	2
pfCluster_C	Env5	1
pfCluster_C	Env4	1

I want to create another table that looks like the following.  Question: How do I generate "Server Capacity Gap"?  Thank you!

pfCluster	# of Customer Environments	Server Capacity Reserve	Server Capacity Gap
pfCluster_A	3	5	2
pfCluster_B	1	2	1
pfCluster_C	2	1	-1

[amitchandak]

@EmeraldLake , for that you need to have context, that you need to force using values or summarize

sumx(values(Table[pfCluster]),[measure]-sum(table[column]))

[EmeraldLake]

@amitchandak Thank you for your reply.  What should I enter for the [measure] and table[column] in the formulat you provided, using the column headers of my sample table?  

[Anonymous]

Hi @EmeraldLake ,

You can't use measure to create table.

# of Customer Environments = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[pfCluster]))

Server CapGap = 'Table'[# of Customer Environments]-'Table'[Server Capacity Reserve in the pfCluster]

Table 2 = DISTINCT(SELECTCOLUMNS('Table',"pfCluster",'Table'[pfCluster],"# of Cx Environments", 'Table'[# of Customer Environments],"Server Cap Reserve",'Table'[Server Capacity Reserve in the pfCluster],"Server CapGap",'Table'[Server CapGap]))

Best regards,

Jay

[EmeraldLake]

somehow the table format was messed up when submitting my question.  Here's the re-post for clarification: 

I've been pounding my head for days over this and also searched online a ton but didn't find a solution to the problem.  

Context/Background of the scenario, simplified for discussion of the Power BI problem: 

- A "pfCluster" can support many customer environments/buildouts. 

- For every customer environment, we need to assign 1 server to manage that customer environment.  And in some pfClusters, we have enough server reserve to manage the customer envirionments, but in others we don't have enough server reserve.  I need to find the "capcaity gap", so to speak

I have a table in Power BI as below, with only columns at the moment - no measure.   

pfCluster           Customer Environment Name                  Server Capacity Reserve in the pfCluster
pfCluster_A                   Env1                                                              5
pfCluster_A                   Env6                                                              5
pfCluster_A                   Env3                                                              5
pfCluster_B                   Env2                                                              2
pfCluster_C                   Env5                                                              1
pfCluster_C                   Env4                                                              1

I want to create another table that looks like the following.  In this example, we have enough server capacity reserve in pfCluster_A and pfCluster_B.  But we're short in pfCluster_C.    

Question: How do I create "# of Customer Environments" and "Server Capacity Gap"?  Thank you!

pfCluster           # of Customer Environments                 Server Capacity Reserve in the pfCluster             Server Capacity Gap
pfCluster_A                   3                                                            5                                                                          2
pfCluster_B                   1                                                            2                                                                          1
pfCluster_C                   2                                                            1                                                                         -1

[EmeraldLake]

2nd table in my post was still messed up 😞  Another try:

I want to create a table like this: 

pfCluster           # of Cx Environments                 Server Cap Reserve             Server CapGap
pfCluster_A                   3                                               5                                             2
pfCluster_B                   1                                               2                                             1
pfCluster_C                   2                                               1                                            -1