Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

augustindelaf

How do you generate complex organization charts in Power BI? The answer is Visio.

The purpose of this article is to explain how to generate an Organization Chart in an industrialized and fast manner, without error and with the minimum of manual interventions, in order to be able to display it in Power BI.

If you prefer a video rather than an article, go to the bottom of this post. Smiley Wink

 

This organization chart should allow you to visualize complex organizations, with large quantities of entities (up to several hundreds), several levels of ownership (ex: level 1: holding company, level 2: company A and company B, level 3: company A1, A2, and B1 and B2), as well as multi-parent relationships (e.g. Company X is owned by both Company A at 49% and Company B at 51%.)

 

In order to make this possible, we describe, in detail, all the steps that are necessary. We unsuccessfully tried many custom visuals (see image below), and the use of Microsoft Visio, through the use of the Organization Chart Wizard, is the optimal solution that we had envisioned.

As far as we know, not any of the following visuals enabled the automatic creation of Multi-Parent diagramsAs far as we know, not any of the following visuals enabled the automatic creation of Multi-Parent diagrams
 
See here what the chart looks like:

Our Organization chart looks like this. "Entité b.3" has 3 parents. No other visual in Power BI provided a Multi-Parent Organization chart like the one possible with Visio.Our Organization chart looks like this. "Entité b.3" has 3 parents. No other visual in Power BI provided a Multi-Parent Organization chart like the one possible with Visio.

Your Source File

This is your Excel source file. If an entity has several parents, just enter the attributes in the same column, separated by commas, i.e. cell D8 contains "Entité B, Entité A, Holding", 3 values of the "Actionnaire" attribute.This is your Excel source file. If an entity has several parents, just enter the attributes in the same column, separated by commas, i.e. cell D8 contains "Entité B, Entité A, Holding", 3 values of the "Actionnaire" attribute.

 

STEP 1

  1. Open a blank Visio file, which is a .vsdx file. 
  2. Go to the Data tab, then click Create.
  3. Select the Simple Flow Diagram option.
  4. Select an Excel workbook (choose its location and then click on Open to import it).
  5. Select a table or a custom range in the workbook: select the relevant range. (NB: it is preferable that your range is formatted in Excel as an array, because the wizard will automatically import the array).

 

1.PNG

6. Click Next.

 

STEP 2

You now need to map your Visio organization chart.

For the process step ID (1) space, select a unique field for each unit. This will be the ID field on our model.

For the description of the process step or activity (2), select a field that would display a description of each entity. On our model, it will be Entity (in order to display the name of the entity).


The form type (3) space for process steps is described in the following section. For the moment, we are inserting the TypeDeForme field. Click Next.


2.PNG
 
STEP 3

Step 3 allows you to assign shapes or icons based on values (e.g. display all entities whose ISO code is FR with a specific form, or all SARL type units with a specific icon, etc.). Our goal in this case is to have a unique type for all entities.

Since we used the TypeForm field, only one value appears. We insert a specific icon (Process) for all entities. In order to insert, a simple dragging and dropping will suffice.


 
3.PNG
 
Select Next to go to step 4.
 
STEP 4

In step 4, you need to map in order to link the units to each other.

In the list that appears, we choose to connect using the column (space 1).

In the next space (2), select the field that identifies the relationships. For our model, the Parent field is used to visualize relationships.

Space 4 (relationship identified by the mapped column) makes it possible to edit the direction of the arrows between the entities (from the subsidiaries to the holding company, or from the holding company to the subsidiaries). By default, select Previous.

You then need to select the delimiter (4). Our data is separated by commas, so we choose commas.

The label space of the connector (5) makes it possible to display information directly on the arrows between the units. Our business need, linked to organization charts, assumes that this information is capital ownership rates. We will insert the Total Detained field.

 
4.PNG
Click Finish. The chart will be generated automatically.
If you would like to format the chart, in order for the visual to be exactly like ours, at the top, contact us.
Click Save, and then select a Microsoft Cloud source (SharePoint, OneDrive) to publish your organization chart.
 
7.PNG
Once the document is saved, copy the SharePoint URL of the document (see image below).
8.PNG
 

The last step takes place in Power BI.
Open Power BI Desktop or the Power BI service and import the Visio Custom Visual
(If you don't know how, please visit this link Smiley Happy:
https://docs.microsoft.com/en-us/power-bi/power-bi-custom-visuals


Once the report is open, click the down arrow on the Field Mapping banner (see screenshot).


9.PNG
 

You now have a full custom organization chart in Power BI!

Thank you for following this tutorial. We hope you enjoyed it.
Please don't hesitate to contact us for any technical issue regarding Power BI & Visio integration.

 

Augustin de la Fouchardiere

Tristan Bruchet


 

MYPE 

https://www.mype-consulting.com/

 

Step 1 : Generate Entity in VisioStep 1 : Generate Entity in Visio

 

Step 2 : Publication in Power BIStep 2 : Publication in Power BI