Forum Discussion

drakwen's avatar
drakwen
New Member
3 years ago
Solved

Distribute a table between %

Hey guys,

 

What would be the best way to achieve the following example?

 

  • Desired Output - Course Engagement distribution
     Count of Unique Employees
    0%-25%10
    25%-50%15
    50%-75%55
    75%-99%25
    100%10

 

  • Source (I don't want to add any calculated columns here since it's a 1.5M row table with 20 columns)
    UniqueIDCourseIDStatus
    g5hfg6hg54sadad223Completed
    g5hfg6hg54asda3166Not Started
    as6d54a6s54asda3166Completed
    as5d6asd566asda1656Not Started
    dsd6f45d677dsada215Not Started

 

  • So, to calculate engagement what we do is:  Tot al Completed Courses / Total Assigned Coursed, which would return as this:
    • g5hfg6hg54 1 Completed / 2 Total = 50%
    • as6d54a6s54 1 Completed / 1 Total = 100%
    • as5d6asd566 1 Not Started / 1 Total = 0%
    • dsd6f45d677 1 Not Started / 1 Total = 0%

 

  • Final table on example is 
     Count of Unique Employees
    0%-25%2
    25%-50% 
    50%-75%1
    75%-99% 
    100%1

 

Many many thanks in advance,

AC.

DAX
SUMMARIZE
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    drakwen 

     

    Are you expecting something like this:

     

    It hides some complexity, we would need to create a segment table like below:

    Based on the Segment table and the orginal Data table, we could get your expected table with a calculated table with the code below:

     

    SegmentCount =
    VAR _table =
    ADDCOLUMNS(
        SUMMARIZE (Data, Data[UniqueID]),
        "PctCount",
        VAR CompleteCount =
            CALCULATE (
                COUNTROWS(
                    FILTER(Data, Data[Status] = "Completed")
                )
            )
        VAR TotalCount =
        CALCULATE (
               COUNTROWS(Data),
               ALLEXCEPT(Data, Data[UniqueID])
         )
        RETURN
            DIVIDE (CompleteCount, TotalCount, 0 )
    )

    RETURN
    ADDCOLUMNS(
        Segment,
        "Count of Unique Employees",
            COUNTROWS(
             FILTER (
                _table,
                AND ([PctCount]>=Segment[Min], [PctCount]<Segment[Max])
            )
        )
    )
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi drakwen ,

    Looking forward to your next reply. If you validate that the method provided by FreemanZ and amitchandak can help you get the solution later, could you please mark it as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

    Best Regards

5 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    drakwen 

     

    Are you expecting something like this:

     

    It hides some complexity, we would need to create a segment table like below:

    Based on the Segment table and the orginal Data table, we could get your expected table with a calculated table with the code below:

     

    SegmentCount =
    VAR _table =
    ADDCOLUMNS(
        SUMMARIZE (Data, Data[UniqueID]),
        "PctCount",
        VAR CompleteCount =
            CALCULATE (
                COUNTROWS(
                    FILTER(Data, Data[Status] = "Completed")
                )
            )
        VAR TotalCount =
        CALCULATE (
               COUNTROWS(Data),
               ALLEXCEPT(Data, Data[UniqueID])
         )
        RETURN
            DIVIDE (CompleteCount, TotalCount, 0 )
    )

    RETURN
    ADDCOLUMNS(
        Segment,
        "Count of Unique Employees",
            COUNTROWS(
             FILTER (
                _table,
                AND ([PctCount]>=Segment[Min], [PctCount]<Segment[Max])
            )
        )
    )
    • drakwen's avatar
      drakwen
      New Member
      3 years ago

      Hi FreemanZ and amitchandak 


      I tried both your methods and both return the samer result, two equal results split into two ranges 25%-50% and 75%-99% for 297. I know that my actual result should be around 6k, so I will try this week to limit the database to 1000 results and play until I discover what is going on with my data, because I am pretty sure both your answers are correct.

       

      Many thanks for both your replies, I'll keep you posted.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi drakwen ,

        Looking forward to your next reply. If you validate that the method provided by FreemanZ and amitchandak can help you get the solution later, could you please mark it as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

        Best Regards