Calculated table with repetitive value count for each id

[Anonymous]

Hey ‌‌:)
My table follows this structure:

Group	Product	VQ	Machine
A	AAA1	10	M1
A	AAA1	8	M2
B	BBB1	8	M1
A	AAA2	10	M1
A	AAA3	10	M1
B	BBB2	8	M2
B	BBB2	8	M2
B	BBB3	8	M1
C	CCC1	5	M2
C	CCC1	5	M2
C	CCC1	5	M2
C	CCC3	5	M2

I would like to create a calculated table that brings me the amount of distinct products with 3 or more repeated VQs, with the same Machine for each Group.

Based on the example table above, this calculated table would look like this:

Group	Number of different Products with 3 or more repeated VQs and same Machine
A	3
B	0
C	0

Explaining the result of the calculated table:

• Group A has 3 distinct products (AAA1, AAA2 and AAA3) with repetitive VQs for the same Machine (all three products has VQ = 10 and Machine = M1).
• Group B has 0 distinct products with 3 or more repetitive VQs for the same Machine (BBB1, BBB2 and BBB3 has VQ =8, but the machines of this this three products is not equal).
• Group C has 0 distinct products with 3 or more repetitive VQs for the same Machine (CCC1 and CCC3 has VQ= 5 and Machine = M2, but its only 2 products in this Group and not 3 or more)
  
  How can I do that?

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

First create a calculated column as follows

_Machine&VQ = 'Data'[Machine  ]&"-"&'Data'[VQ  ] 

then create a calculated table like this:

Table =
VAR _table =
    SUMMARIZE (
        'Data',
        Data[Group ],
        'Data'[_Machine&VQ],
        "countProduct", DISTINCTCOUNT ( Data[Product  ] ),
        "diffCount",
            IF (
                DISTINCTCOUNT ( Data[Product  ] ) >= 3,
                DISTINCTCOUNT ( Data[Product  ] ),
                0
            )
    )
VAR _result =
    SUMMARIZE ( _table, [Group ], [diffCount] )
RETURN
    _result

Display results with matrix visual:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Write this calculated column formula

VQ Machine = Data[VQ  ]&Data[Machine  ]

To your visual, drag the Group column and write this measure

Measure 2 = COALESCE(CALCULATE(DISTINCTCOUNT(Data[Product  ]),FILTER(SUMMARIZE(VALUES(Data[VQ Machine]),[VQ Machine],"ABCD",DISTINCTCOUNT(Data[Product  ]),"EFGH",COUNTROWS(Data)),[ABCD]>=3&&[EFGH]>=3)),0)

Hope this helps.