Building a dashboard

[seba]

Hi! I was wondering if someone could help me build a table as presented on the screenshot. Struggling to achieve the effect I am looking for, as the "number of total cores", as well as "total number of virtual machines -2" does not reflect the sum properly in the table. Some more advanced calculations need to be performed here. Thank you in advance!

[seba]

@Ashish_Mathur @Fowmy @chauhans85 @Anonymous thank you for your help and feedback so far. I was not around for a while hence the delay, apologies. I have attached a table with the data so hopefully it will make it easier. I will do so whenever I ask for help going forward.

The table contains a list of virtual machines (column C) hosted on various servers (column A). Each server can host multiple virtual machines. Column B represents a number of cores per server. To clarify, values in column B for each VM do not add up - serverA is 16 cores, serverB is 24, and serverC is 48 cores.

Server Name	Number of Cores	Virtual Machine Name
Server A	16	VM A
Server A	16	VM B
Server A	16	VM C
Server B	24	VM D
Server B	24	VM E
Server B	24	VM F
Server B	24	VM G
Server C	48	VM H
Server C	48	VM I

My task is to get "expected" total values for the above table. The challange is total value in colum B (cores per server). By "default" Power BI will add number of cores from all rows, and this not what I want. I seem unable to find a function which would count a number of cores per each server but just once no matter how many virtual machines they host. I would like to be able to see value 88 (cores for each server added just once - 16+24+48) for this specific example.

I can see how my previous post were confusing, hope this is clearer and easier to understand. Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Measure = min(Data[Number of cores])

Measure1 = SUMX(VALUES(Data[Server name]),[Measure])

Hope this helps.

[seba]

Let me try explain my challange again as I do not think I have been clear enough. First screenshot shows my raw data in a table visual. There are multiple servers, hosting multiple VMs per each.  Obviously, in the total row (number of cores column) power bi adds up all the rows. What I am trying to achieve is to add a number of cores for each server just once, instead for each VM. So in my case Server A -16, B - 24, C 48, total I would like to see would be 48.

Ideally, I would like to build a table\matrix (whatever is best for this application) similiar to the one on the second picture, but to have the number of cores added as explained above, as well as be able to multiply a value in the second column (I will have to substract number 2 from it first, hence I need a sum so I can substract from it first and then multiply) by the value in the third (I think now it is impossible due to something I do not fully understand).

Much appreciated all the time you spent helping me!

[chauhans85]

Use Matrix Visual instead of Table.

Drag olumns as below
Rows :ServerName, Number of cores in Rows and
Values:count of Virtual Mahine Name. 
not sure about Last column.  or 
USe table and add all three columns into Columns 

and Select Count in Virtual Machine Name
and dont Summurize in No of cores

[Anonymous]

HI @seba,

I'd like to suggest you tried to use 'unpivot columns' feature on this table to transform the table structure to expected results: (you can use 'server name' and 'cores' as the group)

Unpivot columns - Power Query | Microsoft Learn

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

Write these measures

Numberofcores = min(Data[Number of cores])

Measure 1 = countrows(Data)

I cannot understand the last measure.

[seba]

I assume the measure you had trouble understading is "total number of virtual machines -2" which is total number of virtual machines minus number 2 hence the value of this column, apart from total, is 2 less then the colum to the left. I should have been clearer.

[Ashish_Mathur]

Write these measures

Measure 2 = [Measure 1]-2

Measure 3 = SUMX(VALUES(DATA[Server name]),[Measure 2])

Hope this helps.

[seba]

I have added an additional explanation above, hope this will be easier to understand. Thank you!

[Ashish_Mathur]

No, at least i am not clear about what you want.  Sharing an image is useless.  Atleast share data in a format that can be pasted in an MS Excel file.  Show the expected result very cleary.

[Fowmy]

@seba 

Please specify if you need a table visual or a calculated table and what's logic for total number of virtual machines -2

[seba]

The logic behind "total number of virtual machines -2" is total number of virtual machines minus number 2 hence the value of this column, apart from total, is 2 less then the colum to the left.

Please specify if you need a table visual or a calculated table - hmm, is it possible that I need both? At the end I would like to create a table visual with these numbers, but also would this to be calculated (can be behind the scenes), if that makes sense. Thank you!