Forum Discussion

rawiswarden's avatar
rawiswarden
Helper I
6 years ago
Solved

Using Variable Instead of Column Reference

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 meth...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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,

 

  • rawiswarden's avatar
    rawiswarden
    Helper I
    6 years ago

    I'd prefer to only have to change the column in one place, but I'll take what I can get!