Forum Discussion
Dividing data into equal quarters
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.
12 Replies
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"))))Thank you for your reply and assistance- I will try this solution 🙂
Please provide sample data and a depiction of what you are trying to achieve.
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.93it would need to be divded into 4 equal parts with an even gender distribution across the hourly pay quarters. Thanks
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.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
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?
