How to group by ?

[fabd]

Hi,

sorry for this basic question. I hang around the community before posting. I found some help, but i was not able to implement these advice in my case.

So, i have a following table :

I would like to generate a report like this one :

Model , datemonth, CPU, Frequency, RAM

group by model, datemonth, CPU, Frequency, RAM with count of unique model

ie :

Proliant ML350GEN10,      2020-2-11   Intel,  ,2500    ,  512   , 4

Proliant ML350GEN10,      2020-3-14   Intel,  ,2500    ,  512   , 1

I tryed to use SUMMARIZE function with no luck.

I have the following error :

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

if i put 1 element, several are required, and if i put several, another error ...

Can you give me some advice ?

[v-xuding-msft]

Hi @fabd ,

Do you want to create a new table? If so, you could try like this:

Table 2 =
SELECTCOLUMNS (
    'Table',
    "Model", 'Table'[Model],
    "DateMonth", 'Table'[datemonth],
    "CPU", 'Table'[CPU],
    "Frequency", 'Table'[Frequency],
    "RAM", 'Table'[RAM]
)

[fabd]

Hello Xue,

How do you make a group by to count number of Model for each year/month ?

[v-xuding-msft · Accepted solution]

Hello @fabd ,

I'm sorry for my mistake. Please try the formula below.

Table 2 = 
SELECTCOLUMNS (
    'Table',
    "Model", 'Table'[Model],
    "DateMonth", 'Table'[datemonth],
    "CPU", 'Table'[CPU],
    "Frequency", 'Table'[Frequency],
    "RAM", 'Table'[RAM],
    "DistinctCount", CALCULATE (
        DISTINCTCOUNT ( 'Table'[Model] ),
        ALLEXCEPT ( 'Table', 'Table'[datemonth] )
    )
)

I changed the sample data. For more information, see the attachment.

[fabd]

thanks xue, i am going to try

[fabd]

Hi again,

Thanks for the quick tutorial for working with a new table.

I successed to do what i want. Thanks all of you for your help !

[v-xuding-msft]

Hi @fabd ,

Glad you have resolved it.🙂 Can you please share your formula and accept it as a solution? More people who have the same requirement will benefit here.

[amitchandak]

@fabd , Can you share the summarize you have used.

What is left for Distinct count after selecting model

summarize(Table, Table[model], Table[datemonth], Table[CPU], Table[Frequency], Table[RAM] ,"Unique Model", distinctCOUNT(Table[Name]))

[fabd]

Hello guys,

Following your advice, here is the result with the summarize()

For the matrix, this is only what i successed to do

I would like something like this in a simple table that i could export to csv after :

serverModelName,manufacturer,numCpus,cpuType,cpuGhz,memGb,numberOfServers,dateOfPurchase,pricePerServer,
batchType,dataCenterName

[VijayP]

Just Try Creating MAtix Table use rows and Columns to accomodate fields and let me know if any issue and paste the snapshot to see what else you require.

If this is an answer then you can mention this as an answer and share your Kudoes

Regards

Vijay Perepa

you can watch my Videos on www.youtube.com/perepavijay