Filtering by a Specific Percentage

[Alta88]

I need to filter 30% of each of the accounts (Customer ID column) the 'Shellers' (or account-builders) input from the previous day. Right now, I've set the table visual to display accounts tagged as built for the previous day but it shows every single account built by every single Sheller. Do I create a measure? 

[Alta88]

Ok, so having a bit of trouble getting this to work on my end. Has anyone had any success filtering their data by percentages in pbi? 

[Alta88]

Thanks Rico! I'll try this out and see. I'm not very familiar with building in PowerQuery so this may take a bit. 

[v-rzhou-msft · Accepted solution]

Hi @Alta88 ，

Here I suggest you to add an [Index] by group in your table and then create a measure to filter your table visual.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZC5EcAwCAR7IdaMxOnBucvQqP82rIHQR0Cyl+yyt6ABVbU2kyL93qtyCuNIeE/4SPhM+Eq4Jfz5c3V/EuADSIEPnST4MEiDD5NE+LBIBcKKaCG0iBfCi4ghxIgZwoyoIdQyN7P74/MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Tag Date" = _t, #"Customer ID" = _t, #"Customer Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tag Date", type date}, {"Customer ID", Int64.Type}, {"Customer Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Tag Date", "Customer ID"}, {{"Rows", each _, type table [Tag Date=nullable date, Customer ID=nullable number, Customer Name=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Customer Name", "Index"}, {"Custom.Customer Name", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rows"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.Customer Name", "Customer Name"}, {"Custom.Index", "Index"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Index", Int64.Type}, {"Customer Name", type text}})
in
    #"Changed Type1"

New Table:

For reference: Create Row Number for Each Group in Power BI using Power Query

Measure:

Measure1 = 
VAR _COUNTEACHID =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Tag Date], 'Table'[Customer ID] )
    )
VAR _Percentage =
    DIVIDE ( SELECTEDVALUE ( Parameter[Parameter] ), 100 )
VAR _IndexinRange =
    ROUNDDOWN ( _COUNTEACHID * _Percentage, 0 )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Index] ) <= _IndexinRange, 1, 0 )

Here I create a Percentage Slicer by What if Parameter.

Add this measure into the visual level filter in your table vsiual and set it to show items when value =1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.