How can I hide groups that contain one blank or zero value when using Custom Visual Tornado Chart

[ineedham]

When using the tornado chart custom visual, is there an easy way to hide any group in which one of the values is 0 or blank?

For instance, in the example below, how can I make it so January and June values don't even show up in the visualization (without having to filter them out one by one)?

[JayHerrera]

@ineedham - one way would be to use the "Visual Level Filters" and set each to display values greater than zero.  See screenshot for example.

[ineedham]

Thanks. I tried that, but it doesn't work with my specific data. I'm using the tornado to do a gender pay gap analysis. So, I'm averaging salaries of multiple employees.

The Group is the specific position, the Legend is gender, and the Values are the Avg. of the Annual Salary.  Each left bar represents the avg salary of women who hold that position, and the right bar represents the avg salary of men who hold that same position.  Filtering any of these fields to be greater than 0 or not blank doesn't get rid of the blank categories.

[Anonymous · Accepted solution]

@ineedham,

In your scenario, calculate average of Annual Salary in Query Editor, then pivot columns, exclude 0 from the dataset and unpivot columns as the following sample. After, create Tornado again.

1, Original Tornado chart.


2. Pivot columns in Query Editor.


3. Exclude 0 from the dataset.


4. Unpivot columns.



5. Create Tornado chart again.

The above steps generated the code in Advanced Editor, you can test it in your Power BI Desktop.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9BCoUwDESvUgp/ZyRJG6sXcOcJxIVgd/o/eP/Fb8EipVkNzEuYmXW1y35G21kagEJSxz2i+dite9EINCXloUGMwD4pVqaAy+bUnDsBn0loiBeQTNoIEbN/D3P97ljzOV51dRYNPuUpKLDUJ6fBZwbWdtkwKi9lhZal79j+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Legend = _t, Group = _t, Percentage = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Legend", type text}, {"Group", type text}, {"Percentage", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Legend]), "Legend", "Percentage", List.Sum),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([Male] <> 0) and ([Female] <> 0)),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Group"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Regards,
Lydia