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%-7...
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

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

drakwen's avatar
drakwen
New Member
3 years ago

I confirm it now works!! It was my data that was failing