Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is something like this possible in a calculated column? I want to create these sort of bins for multiple columns and want to do it the most efficient way possible. If there is another built-in method in power BI, feel free to share. I just want bins that are somewhat evenly populated.
Solved! Go to Solution.
Hi @rawiswarden ,
Sorry for that We cannot find an effective way to add variables with a column and use it the PERCENTILE.INC function.
But we can simplify the formula as following, If you want to create another measure such as yearly, just chang the Dimension_Portstats[MonthlyIncome] to the column in this formula.
Monthly Income (Bin) =
VAR __Column = Dimension_Portstats[MonthlyIncome]
VAR __25pct = PERCENTILE.INC(Dimension_Portstats[MonthlyIncome], 0.25)
VAR __50pct = PERCENTILE.INC(Dimension_Portstats[MonthlyIncome], 0.50)
VAR __75pct = PERCENTILE.INC(Dimension_Portstats[MonthlyIncome], 0.75)
VAR __25label = "["&min(Dimension_Portstats[MonthlyIncome])&"-"&__25pct&"]"
VAR __50label = "("&__25pct&"-"&__50pct&"]"
VAR __75label = "("&__50pct&"-"&__75pct&"]"
VAR __100label = "("&__75pct&"+]"
RETURN
SWITCH(TRUE,
__Column <= __25pct, __25label,
__Column <= __50pct, __50label,
__Column <= __75pct, __75label,
__100label)
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
Best regards,
Hi @rawiswarden ,
Sorry for that We cannot find an effective way to add variables with a column and use it the PERCENTILE.INC function.
But we can simplify the formula as following, If you want to create another measure such as yearly, just chang the Dimension_Portstats[MonthlyIncome] to the column in this formula.
Monthly Income (Bin) =
VAR __Column = Dimension_Portstats[MonthlyIncome]
VAR __25pct = PERCENTILE.INC(Dimension_Portstats[MonthlyIncome], 0.25)
VAR __50pct = PERCENTILE.INC(Dimension_Portstats[MonthlyIncome], 0.50)
VAR __75pct = PERCENTILE.INC(Dimension_Portstats[MonthlyIncome], 0.75)
VAR __25label = "["&min(Dimension_Portstats[MonthlyIncome])&"-"&__25pct&"]"
VAR __50label = "("&__25pct&"-"&__50pct&"]"
VAR __75label = "("&__50pct&"-"&__75pct&"]"
VAR __100label = "("&__75pct&"+]"
RETURN
SWITCH(TRUE,
__Column <= __25pct, __25label,
__Column <= __50pct, __50label,
__Column <= __75pct, __75label,
__100label)
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
Best regards,
I'd prefer to only have to change the column in one place, but I'll take what I can get!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |