Bottom 25% of values

[Michael1]

Hello,

I love the top n filter, but I need to filter the bottom 25% of values based on a column.  I've been trying to figure out how to do this, but to no avail.  I would really appreciate any help.

Thanks!

Michael

[BhaveshPatel]

Hi Micheal

You can select the BottomN values as well in this functionality but You can not use bottom 25% values. You should try DAX solution or can use Quickcalc functionality.

[Michael1]

Hi Bhavesh,

Thanks for your response.

I created a measure:

    Bottom := percentile.inc ('ELA'[Bmk 1],0.25)

And then created a column:

    Lowest = if ('ELA'[Bmk 1]<=Bottom,1,0)

However, all of my rows are given a 1.

Any ideas?

Thanks again,

Michael

[Sean]

@Michael1 This is probably because your Measure evaluates each row and considers only 1 row at a time!

However if you use the PERCENTILE function in the Column formula it will then evaluate the whole Column [Bmk 1]

Lowest COLUMN = IF ( 'ELA'[Bmk 1] <= PERCENTILE.INC ( 'ELA'[Bmk 1], 0.25 ), 1, 0 )

EDIT: Or change you Measure to evaluate the whole table like this:

Bottom = PERCENTILEX.INC ( ALLSELECTED ( 'ELA' ), 'ELA'[BMK 1], 0.25 )

and then again use in the Column

Lowest = IF ( 'ELA'[Bmk 1] <= [Bottom], 1, 0 )

EDIT2: Or you can just create your bins/buckets like this

Bins =
SWITCH (
    TRUE (),
    'ELA'[Bmk 1] <= PERCENTILE.INC ( 'ELA'[Bmk 1], 0.25 ), "Bottom",
    'ELA'[Bmk 1] <= PERCENTILE.INC ( 'ELA'[Bmk 1], 0.75 ), "Middle",
    'ELA'[Bmk 1] <= PERCENTILE.INC ( 'ELA'[Bmk 1], 1.00 ), "Top"
)

[Michael1]

Thank you so much!  What if I want the values to change based on slicers selected by the end user?

[v-yulgu-msft]

Hi @Michael1,

Instead of adding a calculate column, please use measure:

Bottom = PERCENTILEX.INC ( ALLSELECTED( Table3 ), Table3[col1], 0.25 )

Lowest = CALCULATE(IF(SUM('Table3'[col1])<='Table3'[Bottom],1,0))

Thanks,
Yuliana Gu

[Michael1]

Hi Yuliana,

Thank you for your response.  I tried to make them measures, but then receive this error message.

Essentially, I think my issue might be because I have this table set with several relationships to other tables.

Any thoughts as to how I might get ths to work?

Thank you so much!

Michael

[Sean]

I get this error in the 32-bit Excel. On the same computer, the same data model but in the 64-bit PBI runs with no problems.

[Michael1]

Sean,

Thanks.  It seems as though I am running the 64-bit version.  Is there a different version to install?

I appreciate everyone's help!

Michael

[v-yulgu-msft]

Hi @Michael1,

Click this link, you can download the PowerBI desktop for 32-bit.
https://www.microsoft.com/en-us/download/details.aspx?id=45331

Thanks,
Yuliana Gu

[Michael1]

Hi Yuliana,

I tried the 32-bit version and get the same error that I should use the 64-bit version.  I sent a "frown" to Microsoft and was told that this is a known bug that should be fixed in an upcoming version.

I'll keep this thread updated as to my progress.

Thank you!

Michael