How to dynamically filter rows in a matrix based on a percent of the column total

[Anonymous]

Hello,

I have a matrix with 20 rows, each row representing a different group of people I've mailed.  The mailed column is simply the total number of people mailed in each group.  At the bottom of the matrix is the total number of mailed items.  I know I can create a simple visual filter using the Mailed column and specify something like "Show items when the value is greater than 5000".  However, I don't want to filter based on a constant.  I want to display rows if the number of items mailed is greater than a percentage of the TOTAL because sometimes I mail 50,000 units to the 20 groups, and sometimes I might mail 400,000 units.  

So my question is in two parts: 

1.   Would you please show me how to create that dynamic filter based on the total of the Mailed column, where the filter determines whether to display that row/group in the matrix?
2.   Would you please show me how to apply it to the matrix AND a line chart on the same page (i.e., the line chart "Values" field contains a different measure that uses the units mailed in each row in its calculation).

Thank you.

Table with 20 rows

[Cary_Casey · Accepted solution]

First make sure you're using a table.

Second adapt all the below measures to your model:

Sum_Mailed = Sum(Test[Mailed])

The sum of the number value in the mailed column that will have row context in the visuals. I think you used countrows in your example, but you could add a column with 1 populating on non blanks and sum on that.

Sum_Mailed_ALLROWS = CALCULATE(Sum(Test[Mailed]), ALL(Test[Group]))

This will do the same as the previous but ignore row context.

Percent% = [Sum_Mailed]/[Sum_Mailed_ALLROWS]

Gives a % of the total value that we will compare to a dynamic value later to filter the tables.

Selected Percent = SELECTEDVALUE('Percent Filter'[Index])

Dynamic value to filter by

Greater Than X Percent =

VAR MailedSum = [Sum_Mailed_ALLROWS]

RETURN

IF([Sum_Mailed]/MailedSum > [Selected Percent], 1, 0)

This is where the magic happens that is simultaneously dynamically calculated with static elements. If the dynamic sum of mailed (with row context) divided by the static sum of mailed (no row context just all) is greater than [Selected Percent] then 1 else 0.

Make sure to add the Greater Than X Percent to your table (not a matrix) and on the filter pane change it to greater than or equal to 1. The column itself does not need to be displayed. First turn of the column header word wrap then change the size of the Greater Than X Percent column to as small as it will go.

Finally here is the M code for the Percent Filter Table (just a table with .00 to .99):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Divided Column" = Table.TransformColumns(#"Changed Type", {{"Column1", each _ / 10, type number}}),
    #"Added Custom" = Table.AddColumn(#"Divided Column", "Custom", each 10),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Repeat( {[Column1] },  [Custom])),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom.1", "Index", 0, 1, Int64.Type),
    #"Divided Column1" = Table.TransformColumns(#"Added Index", {{"Index", each _ / 100, type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Divided Column1",{"Column1", "Custom", "Custom.1"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Index", "Index - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Index - Copy", type text}})
in
    #"Changed Type1"

 With the above table, you can add a slicer that is searchable using the text version of the column and dynamically changes how the table is filtered based on any decimal value(or percent if you change the column type)within the table.

Picture of finished product:

Hope this helps.

Kudos to @AlB for the SQLBI resource great stuff and very similiar to my proposed solution.

[AlB]

Hi @Anonymous 

You can create a measure that returns for instance a 1 when the row is to be shown and a 0 when it's not. Then use that measure as filter for both visuals. Check this out:

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers