Forum Discussion

vkomarag's avatar
vkomarag
Icon for Helper III rankHelper III
9 years ago
Solved

Individual and Cumulative in a single visual

Hi

 

For example on employee table, I need to show the number of employees in each department as well as total employees in a single visual.

 

I am able to do it in different visuals and keep side by side. Just checking if there is any possibility to keep them in a single visual.

 

Thanks

KVB

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago


    Hi vkomarag,

    You can create two measure using the formula.

    Department = COUNTA(Table1[Employee])
Total = CALCULATE ( COUNTA ( Table1[Employee] ), ALL (Table1 ) )

    And select them as value field in bar chart. Please see the following screenshot, I use Cluster column chart, if you want to other bar chart, you can change it.

    Thanks,
    Angelia

5 Replies

  • Reid_Havens's avatar
    Reid_Havens
    Icon for Most Valuable Professional rankMost Valuable Professional
    9 years ago

    Hi there,

     

    You should be able to create a measure that concatenates two values. One for the total count of that column, and one for the count of rows of that department. It could look something like this:

     

    Count of Employees

    =COUNTA(Table[EmployeeID])

     

    Count of ALL Employees

    =
CALCULATE ( COUNTA ( Table[EmployeeID] ), ALL ( Table ) )

     

    Concatenated Values

    =
    [Count of Employees] & " / " & [Count of ALL Employees] 

     

    You can this use this on a single card style or other visual. Hope this helps! 

    • dkay84_PowerBI's avatar
      dkay84_PowerBI
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      You can also use Tooltips to display the total on hover, where the actual components of the visual (i.e. columns or bars) show the value per department.  Lastly, you can use a stacked column with department as legend to visually show the total (height of column) and breakdown by department (colors).

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi vkomarag,

    After test using the solution dkay84_PowerBI posted using the following sample table.



    Then you get expected result.



    If you still have problem, please share your sample data for further analysis.

    Best Regards,
    Angelia

    • vkomarag's avatar
      vkomarag
      Icon for Helper III rankHelper III
      9 years ago

      Actually I need it in a bar chart in vertical representation.

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago


        Hi vkomarag,

        You can create two measure using the formula.

        Department = COUNTA(Table1[Employee])
Total = CALCULATE ( COUNTA ( Table1[Employee] ), ALL (Table1 ) )

        And select them as value field in bar chart. Please see the following screenshot, I use Cluster column chart, if you want to other bar chart, you can change it.

        Thanks,
        Angelia