Forum Discussion

ineedham's avatar
ineedham
Icon for Helper I rankHelper I
9 years ago
Solved

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

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)?

 

 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

3 Replies

  • JayHerrera's avatar
    JayHerrera
    Icon for Advocate I rankAdvocate I
    9 years ago

    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's avatar
      ineedham
      Icon for Helper I rankHelper I
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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