Forum Discussion

Atanas_Atanasov's avatar
Atanas_Atanasov
Icon for Helper II rankHelper II
3 years ago
Solved

Counting People Based On Maximum Result

Dear All,
I am really struggling with one calculation:
Got the table from screenshot 1 where I have number of cases breakdown by person. In the rows I have a name and in the columns I have the BU:
Based on the number of cases I need to assign the person to the business unit where they have most of the cases. As an example the first person who has 52 cases in "Storage" has to be assigned to "Storage" the following one has 53 in Compute and some other numbers for the rest, so they have to be counted in "Compute".
As the original requirement was to create a measure that assigns the person to the relevant BU I managed to do it (screen shot 2).
This is the code I used to create the BU Name measure:
BU Name =
VAR MaxCases =
    MAXX( VALUES( 'Closed Cases'[BU (groups)] ), [Total Case Volume] )
RETURN
CALCULATE(
    SELECTEDVALUE( 'Closed Cases'[BU (groups)] ),
    FILTER(
        VALUES( 'Closed Cases'[BU (groups)] ),
        [Total Case Volume] = MaxCases ))
But now I need to count them by business unit based on the max result for that business unit. Basically same calculation but vice versa (I think)
So the result must look like screenshot 3, but for compute I am sure I need to show 161, not 213...
Can anyone help please?
Thank you in advance Atanas

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Atanas_Atanasov ,

     

    Here I create a sample to have a test.

    Measure:

    Engineer_Count = 
VAR _STEP1 =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Engineer Email],
        'Table'[BU Name],
        "Count", COUNT ( 'Table'[Engineer Email] )
    )
VAR _STEP2 =
    FILTER (
        _STEP1,
        [Count]
            = MAXX (
                FILTER ( _STEP1, [Engineer Email] = EARLIER ( [Engineer Email] ) ),
                [Count]
            )
    )
RETURN
    COUNTX (
        FILTER ( _STEP2, [BU Name] = MAX ( 'DimBU Name'[BU Name] ) ),
        [Engineer Email]
    ) + 0

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

11 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi, Atanas_Atanasov 

     

    It seems like your current measure already correctly assigns a person to their respective BU based on the maximum case volume. What you need to do next is create another measure to count the number of people per BU.

    This is essentially a COUNTAX function across a filtered table where the BU in the row context equals the BU assigned to each person.

     

    for example,
    BU Count =
    COUNTAX (
    FILTER (
    ALL ( 'Closed Cases' ),
    [BU Name] = SELECTEDVALUE ( 'Closed Cases'[BU (groups)] )
    ),
    [Person]
    )


     

    Please replace [Person] with the actual column name in your data which represents a person or a case.

     

      • rubayatyasmin's avatar
        rubayatyasmin
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Sorry to hear that. 

        try this one,

         

        BU Count =
        SUMX(
        VALUES('Closed Cases'[Person]),
        IF (
        CALCULATE (
        COUNTROWS('Closed Cases'),
        ALL('Closed Cases'),
        'Closed Cases'[Person] = EARLIER('Closed Cases'[Person]),
        'Closed Cases'[BU (groups)] = [BU Name]
        ) > 0,
        1,
        0
        )
        )

         

        assuming that 'Closed Cases' is your data table, 'Person' is the column that contains the name of the person, and 'BU Name' is your already calculated measure.

         

         

  • bot_damo's avatar
    bot_damo
    Frequent Visitor
    3 years ago

    Hi Atanas,

    Can you please share how you're getting to the value you need to show '161'

    Kind Regards,
    Damian

    • Atanas_Atanasov's avatar
      Atanas_Atanasov
      Icon for Helper II rankHelper II
      3 years ago

      Dear bot_damo  - I am counting them by hand from the second screenshot. I filtered it ony by compute I counted 161 rows with "Compute".

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Atanas_Atanasov ,

     

    Here I create a sample to have a test.

    Measure:

    Engineer_Count = 
VAR _STEP1 =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Engineer Email],
        'Table'[BU Name],
        "Count", COUNT ( 'Table'[Engineer Email] )
    )
VAR _STEP2 =
    FILTER (
        _STEP1,
        [Count]
            = MAXX (
                FILTER ( _STEP1, [Engineer Email] = EARLIER ( [Engineer Email] ) ),
                [Count]
            )
    )
RETURN
    COUNTX (
        FILTER ( _STEP2, [BU Name] = MAX ( 'DimBU Name'[BU Name] ) ),
        [Engineer Email]
    ) + 0

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

    • Atanas_Atanasov's avatar
      Atanas_Atanasov
      Icon for Helper II rankHelper II
      3 years ago

      Anonymous  - Thank you so much for your suggestion!

      I've tried the following code you suggested (please note I am using one - single table:

      Eng Count =
      VAR Step1 =
      SUMMARIZE(
          ALL( 'Closed Cases' ),
          'Closed Cases'[Engineer Email],
          'Closed Cases'[BU (groups)],
          "Count", COUNT( 'Closed Cases'[Engineer Email] ))

      VAR Step2 =
      FILTER(
          Step1,
          [Count] = MAXX(
              FILTER(
                  Step1, 'Closed Cases'[Engineer Email] = EARLIER( 'Closed Cases'[Engineer Email] )), [Count] ))
      RETURN
      COUNTX(
          FILTER(
              Step2, 'Closed Cases'[BU (groups)] = MAX( 'Closed Cases'[BU (groups)] )), 'Closed Cases'[Engineer Email] ) + 0
      but this is the result I get:

      All due respect, 

      Atanas