Forum Discussion

josegovia's avatar
josegovia
Regular Visitor
9 years ago
Solved

Two variables average over a bar graph.

Hello   I want to add several variables to a bar chart. The variables contain values that are between 0 and 10, and I want the chart show the mean of each variable.   The label of each bar must b...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi josegovia,

     

    Suppose your source table looks like:

     

    You can use a clustered column chart to show the average values for satisfaction and knowledge. But the limitation is that it is not possible to show the label as the name of the variable or a custom value, data labels can only show the numeric values for each bar.

     

    As a workaround, you can unpivot the table structure first in query editor mode. Select the [Case type] column, then from Transform menu tab, choose 'Unpivot other columns' from the drop down list 'Pivot Column'. Then, you will get a table as below.

     

    Best regards,
    Yuliana Gu

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi josegovia,

     

    To keep the original table structure, you can replicate a new table, then, you can unpivot columns based on this new table.

     

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi josegovia,

 

Suppose your source table looks like:

 

You can use a clustered column chart to show the average values for satisfaction and knowledge. But the limitation is that it is not possible to show the label as the name of the variable or a custom value, data labels can only show the numeric values for each bar.

 

As a workaround, you can unpivot the table structure first in query editor mode. Select the [Case type] column, then from Transform menu tab, choose 'Unpivot other columns' from the drop down list 'Pivot Column'. Then, you will get a table as below.

 

Best regards,
Yuliana Gu

  • josegovia's avatar
    josegovia
    Regular Visitor
    9 years ago

    Thanks for the solution.

     

    But three things happen to me:

     

    • The first is that the main table disappears and I would like to keep it because I have more varibales in it.
    • The second is that if I keep the two tables, I understand that they will have to be related so that if I make a filter in some data affect all the graphs.
    • And the third is that I can not select "Average" from the "Value". I only have COUNT and COUNT DISTINT options.

    Can you help me with these three questions?

     

    Thank you very much.

     

    José

    • josegovia's avatar
      josegovia
      Regular Visitor
      9 years ago

      Well, I've already solved the first one. I have changed the format of the varibal to "Whole Number".

       

      But I need help with the other two.

       

      Thank you.

       

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        9 years ago

        Hi josegovia,

         

        To keep the original table structure, you can replicate a new table, then, you can unpivot columns based on this new table.

         

        Best regards,
        Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    This is the same problem I'm facing. I can't select "Averge of Value". My only two options are "Count" or "Distinct Count" or to create a new quick measure.

     

    Is the average option in the orinigal post no longer available?

     

    Thanks so much!