Hello, I have the data as below : categorySub CategoryHostModelAPTTYV1VCL_MU_FGTHost 1C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 2C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 3C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 4C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 5C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 6C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 7C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 8C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 9C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 10C4580_Ec7502_1_32APTTYV1VCL_MU_FGTHost 11D4000_c7502_1_32APTTYV1VCL_MU_FGTHost 12D4000_c7502_1_32APTTYV1VCL_MU_FGTHost 13D4000_c7502_1_32APTTYV1VCL_MU_FGTHost 14D4000_c7502_1_32APTTYV1VCL_MU_FGTHost 15D4000_c7502_1_32APTTYV1VCL_MU_FGTHost 16R2000_c7502_1_32APTTYV1VCL_MU_FGTHost 17R2000_c7502_1_32APTTYV1VCL_MU_FGTHost 18R2000_c7502_1_32APTTYV1VCL_MU_FGTHost 19R2000_c7502_1_32APTTYV1VCL_MU_FGTHost 20R2000_c7502_1_32APTTYV1NCL_MU_F30Host 21C4580_Ec7502_1_32APTTYV1NCL_MU_F30Host 22A2000_c7502_1_32APTTYV1NCL_MU_F30Host 23A2000_c7502_1_32APTTYV1NCL_MU_F30Host 24A2000_c7502_1_32APTTYV1BVCL_RG_AZEHost 25C4580_Ec7502_1_32APTTYV1BVCL_RG_AZEHost 26C4580_Ec7502_1_32APTTYV1BVCL_RG_AZEHost 27A2000_c7502_1_32APTTYV1BVCL_RG_AZEHost 28A2000_c7502_1_32APTTYV1BVCL_RG_AZEHost 29A2000_c7502_1_32APTTYV1BVCL_RG_AZEHost 30R2000_c7502_1_32APTTYV1BVCL_RG_AZEHost 31R2000_c7502_1_32APTTYV1BVCL_RG_AZEHost 32R2000_c7502_1_32APTTYV1BVCL_RG_AZEHost 33R2000_c7502_1_32APTTYV1BVCL_RG_AZEHost 34E2030_c7502_1_32 I would like to create the table/Matrix as below:categorySub CategoryNumber of ModelNb VMVM WindowsVM LinuxVM otherNb HostModel 1Nb HostModel 2Nb HostModel 3Nb HostModel 4Nb HostAPTTYV1VCL_MU_FGT372007101020C4580_Ec7502_1_3210D4000_c7502_1_325R2000_c7502_1_325 APTTYV1NCL_MU_F30220020170104C4580_Ec7502_1_321A2000_c7502_1_323 APTTYV1BVCL_RG_AZE4200201701010C4580_Ec7502_1_322A2000_c7502_1_323R2000_c7502_1_324E2030_c7502_1_321 1. The columns "Model 1, 2, 3 ,4 " is the list of avalaible models of each "Sub-category"2. "Nb Host" is total of Host of each Model Could you please advise? Thank you in advance.

Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Create custom column in a table

Hello,

I have the data as below :

category	Sub Category	Host	Model
APTTYV1V	CL_MU_FGT	Host 1	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 2	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 3	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 4	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 5	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 6	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 7	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 8	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 9	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 10	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 11	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 12	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 13	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 14	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 15	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 16	R2000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 17	R2000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 18	R2000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 19	R2000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 20	R2000_c7502_1_32
APTTYV1N	CL_MU_F30	Host 21	C4580_Ec7502_1_32
APTTYV1N	CL_MU_F30	Host 22	A2000_c7502_1_32
APTTYV1N	CL_MU_F30	Host 23	A2000_c7502_1_32
APTTYV1N	CL_MU_F30	Host 24	A2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 25	C4580_Ec7502_1_32
APTTYV1BV	CL_RG_AZE	Host 26	C4580_Ec7502_1_32
APTTYV1BV	CL_RG_AZE	Host 27	A2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 28	A2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 29	A2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 30	R2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 31	R2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 32	R2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 33	R2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 34	E2030_c7502_1_32

I would like to create the table/Matrix as below:

category	Sub Category	Number of Model	Nb VM	VM Windows	VM Linux	VM other	Nb Host	Model 1	Nb Host	Model 2	Nb Host	Model 3	Nb Host	Model 4	Nb Host
APTTYV1V	CL_MU_FGT	3	720	0	710	10	20	C4580_Ec7502_1_32	10	D4000_c7502_1_32	5	R2000_c7502_1_32	5
APTTYV1N	CL_MU_F30	2	200	20	170	10	4	C4580_Ec7502_1_32	1	A2000_c7502_1_32	3
APTTYV1BV	CL_RG_AZE	4	200	20	170	10	10	C4580_Ec7502_1_32	2	A2000_c7502_1_32	3	R2000_c7502_1_32	4	E2030_c7502_1_32	1

1. The columns "Model 1, 2, 3 ,4 " is the list of avalaible models of each "Sub-category"

2. "Nb Host" is total of Host of each Model

Could you please advise?

Thank you in advance.

Need Help

Anonymous

3 years ago

Hi Anonymous ,

Please create below measure, and add it to table visual.

Number of Model =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
    )
RETURN
    DISTINCTCOUNT ( [Model] )

Model 1 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
    )
RETURN
    MAXX ( tmp, [Model] )

Nb Host For Model 1 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 1]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] = cur_model
    )
RETURN
    COUNTROWS ( tmp )

Model 2 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] <> pre_model1
    )
RETURN
    MAXX ( tmp, [Model] )

Nb Host For Model 2 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 2]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] = cur_model
    )
RETURN
    COUNTROWS ( tmp )

Model 3 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR pre_model2 = [Model 2]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] <> pre_model1
            && 'Table'[Model] <> pre_model2
    )
RETURN
    IF ( ISEMPTY ( tmp ), BLANK (), MAXX ( tmp, [Model] ) )

Nb Host For Model 3 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 3]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] = cur_model
    )
RETURN
    IF ( ISEMPTY ( tmp ), BLANK (), COUNTROWS ( tmp ) )

Model 4 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR pre_model2 = [Model 2]
VAR pre_model3 = [Model 3]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] <> pre_model1
            && 'Table'[Model] <> pre_model2
            && 'Table'[Model] <> pre_model3
    )
RETURN
    IF ( ISEMPTY ( tmp ), BLANK (), MAXX ( tmp, [Model] ) )

Nb Host For Model 4 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 4]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] = cur_model
    )
RETURN
    IF ( ISEMPTY ( tmp ), BLANK (), COUNTROWS ( tmp ) )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Create custom column in a table.pbix28 KB

5 Replies

Anonymous

Not applicable

3 years ago

Hi Anonymous ,

Please create below measure, and add it to table visual.

Number of Model =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
    )
RETURN
    DISTINCTCOUNT ( [Model] )

Model 1 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
    )
RETURN
    MAXX ( tmp, [Model] )

Nb Host For Model 1 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 1]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] = cur_model
    )
RETURN
    COUNTROWS ( tmp )

Model 2 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] <> pre_model1
    )
RETURN
    MAXX ( tmp, [Model] )

Nb Host For Model 2 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 2]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] = cur_model
    )
RETURN
    COUNTROWS ( tmp )

Model 3 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR pre_model2 = [Model 2]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] <> pre_model1
            && 'Table'[Model] <> pre_model2
    )
RETURN
    IF ( ISEMPTY ( tmp ), BLANK (), MAXX ( tmp, [Model] ) )

Nb Host For Model 3 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 3]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] = cur_model
    )
RETURN
    IF ( ISEMPTY ( tmp ), BLANK (), COUNTROWS ( tmp ) )

Model 4 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR pre_model2 = [Model 2]
VAR pre_model3 = [Model 3]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] <> pre_model1
            && 'Table'[Model] <> pre_model2
            && 'Table'[Model] <> pre_model3
    )
RETURN
    IF ( ISEMPTY ( tmp ), BLANK (), MAXX ( tmp, [Model] ) )

Nb Host For Model 4 =
VAR cur_category =
    SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
    SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 4]
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = cur_category
            && 'Table'[Sub Category] = cur_sub_category
            && 'Table'[Model] = cur_model
    )
RETURN
    IF ( ISEMPTY ( tmp ), BLANK (), COUNTROWS ( tmp ) )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Create custom column in a table.pbix28 KB

Anonymous

Not applicable

3 years ago

Anonymous

Thank you for your solution, it works well until I filter by Catagory. This slicer uses Catagory column in another table which has the relation with this table.

When I apply the filter, the Model is empty but all other columns have value:

How could I replace the column Category of other table in all the measures that we've just created?

MAny thanks in advance.

Anonymous

Not applicable

3 years ago

Anonymous

Could you please advise how to add the total of HOST of each model ?

I have another column name CPO as below, could you help to calculate the sum of CPO of each Model ?

categorySub CategoryHostModelcpO

APTTYV1V	CL_MU_FGT	Host 1	C4580_Ec7502_1_32	2
APTTYV1V	CL_MU_FGT	Host 2	C4580_Ec7502_1_32	3
APTTYV1V	CL_MU_FGT	Host 3	C4580_Ec7502_1_32	4
APTTYV1V	CL_MU_FGT	Host 4	C4580_Ec7502_1_32	1
APTTYV1V	CL_MU_FGT	Host 5	C4580_Ec7502_1_32	2
APTTYV1V	CL_MU_FGT	Host 6	C4580_Ec7502_1_32	33
APTTYV1V	CL_MU_FGT	Host 7	C4580_Ec7502_1_32	2
APTTYV1V	CL_MU_FGT	Host 8	C4580_Ec7502_1_32	6
APTTYV1V	CL_MU_FGT	Host 9	C4580_Ec7502_1_32	8
APTTYV1V	CL_MU_FGT	Host 10	C4580_Ec7502_1_32	12
APTTYV1V	CL_MU_FGT	Host 11	D4000_c7502_1_32	9
APTTYV1V	CL_MU_FGT	Host 12	D4000_c7502_1_32	8
APTTYV1V	CL_MU_FGT	Host 13	D4000_c7502_1_32	14
APTTYV1V	CL_MU_FGT	Host 14	D4000_c7502_1_32	2
APTTYV1V	CL_MU_FGT	Host 15	D4000_c7502_1_32	3
APTTYV1V	CL_MU_FGT	Host 16	R2000_c7502_1_32	4
APTTYV1V	CL_MU_FGT	Host 17	R2000_c7502_1_32	22
APTTYV1V	CL_MU_FGT	Host 18	R2000_c7502_1_32	24
APTTYV1V	CL_MU_FGT	Host 19	R2000_c7502_1_32	33
APTTYV1V	CL_MU_FGT	Host 20	R2000_c7502_1_32	2
APTTYV1N	CL_MU_F30	Host 21	C4580_Ec7502_1_32	12
APTTYV1N	CL_MU_F30	Host 22	A2000_c7502_1_32	12
APTTYV1N	CL_MU_F30	Host 23	A2000_c7502_1_32	12
APTTYV1N	CL_MU_F30	Host 24	A2000_c7502_1_32	8
APTTYV1BV	CL_RG_AZE	Host 25	C4580_Ec7502_1_32	12
APTTYV1BV	CL_RG_AZE	Host 26	C4580_Ec7502_1_32	9
APTTYV1BV	CL_RG_AZE	Host 27	A2000_c7502_1_32	8
APTTYV1BV	CL_RG_AZE	Host 28	A2000_c7502_1_32	14
APTTYV1BV	CL_RG_AZE	Host 29	A2000_c7502_1_32	2
APTTYV1BV	CL_RG_AZE	Host 30	R2000_c7502_1_32	3
APTTYV1BV	CL_RG_AZE	Host 31	R2000_c7502_1_32	4
APTTYV1BV	CL_RG_AZE	Host 32	R2000_c7502_1_32	22
APTTYV1BV	CL_RG_AZE	Host 33	R2000_c7502_1_32	24
APTTYV1BV	CL_RG_AZE	Host 34	E2030_c7502_1_32	33

Thank you in advance.

Greg_Deckler

Community Champion

3 years ago

Anonymous Try:

Number of Models Measure = 
  COUNTROWS(DISTINCT(SELECTCOLUMNS('Table',"category",[category],"subcategory",[Sub Category],"model",[Model])))


Nb VM Measure = COUNTROWS(DISTINCT('Table'))

Anonymous
Not applicable
3 years ago
Hi Greg_Deckler

I guess I wasn't clear enough.

The columns I would like to add is in blue and orange .
- The available model listed in the columns Model 1, Model 2, Model 3, Model 4 . Normally a Sub-category has max 4 model
- The number of host of each Sub-category by Model

Forum Discussion

Create custom column in a table

5 Replies

Related content

Creating a custom column

create custom column concatenated

Creating Custom columns

Creating custom columns keeps returning a structured Table column

Create a custom column