topic Re: Distribute a table between % in DAX Commands and Tips

Distribute a table between %

drakwen — Fri, 11 Nov 2022 16:27:43 GMT

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)
UniqueID CourseID Status
g5hfg6hg54 sadad223 Completed
g5hfg6hg54 asda3166 Not Started
as6d54a6s54 asda3166 Completed
as5d6asd566 asda1656 Not Started
dsd6f45d677 dsada215 Not 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.

Re: Distribute a table between %

amitchandak — Sat, 12 Nov 2022 02:28:38 GMT

drakwen , You need dynamic segmentation for that

Divide( Count(filter(Table[Status] ="Completed"), Table[CourseID]) , Count(Table[CourseID]) )

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Re: Distribute a table between %

FreemanZ — Sun, 13 Nov 2022 03:46:24 GMT

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])

)

Re: Distribute a table between %

drakwen — Mon, 14 Nov 2022 08:50:47 GMT

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.

Re: Distribute a table between %

Anonymous — Tue, 15 Nov 2022 08:13:50 GMT

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

Re: Distribute a table between %

drakwen — Thu, 17 Nov 2022 19:09:08 GMT

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

	Count of Unique Employees
0%-25%	10
25%-50%	15
50%-75%	55
75%-99%	25
100%	10

UniqueID	CourseID	Status
g5hfg6hg54	sadad223	Completed
g5hfg6hg54	asda3166	Not Started
as6d54a6s54	asda3166	Completed
as5d6asd566	asda1656	Not Started
dsd6f45d677	dsada215	Not Started