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 complic...
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")
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.
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