Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated table with repetitive value count for each id

Hey ‌‌:)
My table follows this structure:

Group Product  VQ  Machine  
AAAA110 M1
AAAA18 M2
BBBB18 M1
AAAA210 M1
AAAA310 M1
BBBB28 M2
BBBB28 M2
BBBB38 M1
CCCC15 M2
CCCC15 M2
CCCC15 M2
CCCC35 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?
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    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.

     

     

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.