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 J...
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

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