Dividing data into equal quarters

[Naomig2088]

Hi,

I'm very new to PBI so any help would be much appreciated. I'm trying to break my data which shows hourly rate of each employee into 4 equal quarters, based on the hourly rate. The added complication is that the data I'm using shows the gender of each hourly rate, so I need to ensure there is an even gender distribution across the hourly pay quarters. Can anyone suggest anything please? Thanks.

[Anonymous]

Hi @Naomig2088  did you get this resolved i also have the same question would be great if you could provide the formula if you did manage to  resolve this query? 

[V-pazhen-msft]

@Naomig2088 

To evenly distribute by genders, you would need to add an index to each gender in power query. Then you can create a column to split the table into 4 quarters evenly. Check the sample pbix for the steps.

Quarter =
var splitby= CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Gender]))/ 4
Return SWITCH(TRUE(),
[Grouped.GroupIndex]<=splitby,"Q1",
[Grouped.GroupIndex]>splitby && [Grouped.GroupIndex]<=splitby*2,"Q2",
[Grouped.GroupIndex]>splitby*2 && [Grouped.GroupIndex]<=splitby*3,"Q3",
[Grouped.GroupIndex]>splitby*3 && [Grouped.GroupIndex]<=splitby*4,"Q4")​

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi how did you do the grouped index please? Can you show the dax or sample file and also how do i rank hourly rates from highest to smallest with a number

[Naomig2088]

Thanks for you reply. I cannot seem to add the index to the gender in the way that you have. Also, the hourly rates also need to be distributed across the quarters from lowest to highest. Thanks

[vaishnaviudan]

Hi,

You can divide Hourly Rate data based on Number of Quarter.

I tried to divide in four quarter based on given conditions:

Quarter 1: Hourly Rate>=0 and Hourly Rate<=5

Quarter 2:Hourly Rate>5 and Hourly Rate<=10

Quarter 3:Hourly Rate>10 and Hourly Rate<=15

Quarter 4:Hourly Rate>15 and Hourly Rate<=100

You can use following DAX to create quarterwise data:

Quarter = IF(AND('Table'[Hourly Rate]>=0,'Table'[Hourly Rate]<=5), "Quarter 1",

IF(AND('Table'[Hourly Rate]>5,'Table'[Hourly Rate]<=10), "Quarter 2",

IF(AND('Table'[Hourly Rate]>10,'Table'[Hourly Rate]<=15),"Quarter 3",

IF(AND('Table'[Hourly Rate]>15,'Table'[Hourly Rate]<=100),"Quarter 4"))))

[Naomig2088]

Thank you for your reply and assistance- I will try this solution 🙂

[PaulDBrown]

Please provide sample data and a depiction of what you are trying to achieve.

[Naomig2088]

Thanks for you reply. Here is a small sample:

Id	Gender	Hourly Rate
1110	Female	11.1
1111	Male	24.71
1112	Female	17.31
1113	Female	8.94
1114	Male	9.63
1115	Male	9.63
1116	Female	8.43
1117	Female	9.51
1118	Female	9.63
1119	Female	8.94
1120	Male	10.49
1121	Male	13.49
1122	Female	8.94
1123	Female	9.63
1124	Female	8.94
1125	Female	17.55
1126	Male	9.43
1127	Female	14.91
1128	Female	12.35
1129	Male	9.43
1130	Female	54.48
1131	Female	15.24
1132	Female	15.93

[Naomig2088]

it would need to be divded into 4 equal parts with an even gender distribution across the hourly pay quarters. Thanks

[PaulDBrown]

Thank you for providing data.

What do you mean by "4 equal parts" etc...?

It would be really helpful if you posted a depiction of the expected result

[Naomig2088]

So, I would need to divide the data into 4 equal quarters based on hourly rate, lowest to highest. As an outcome, I would need a table with the number of employees in each quarter, with their hourly rate and would need for the gender to be taken into account- there are 7 males and 16 females so they would need to be distributed evenly across the hourly pay quartiles. I hope that makes sense!

[PaulDBrown]

Still not sure what you need...