Need help with Hierarchy with three levels and reporting in a column chart

[JasperWilli]

Hi everyone,

I an super new to Power BI and not familiar with how to solve for a problem I have. I'm using a SharePoint list that tracks project progress as my data source. In it there are three levels:

The project (list item) which contains a column for the percent completed. That project rolls up to a column called Goal, and goals roll up to something I'm calling Pillar, which is the highest level of the project portfolio. I need to show progress at the Pillar level and have very little idea how to make that happen. Can someone give me some pointers or links on how to make this happen?

Thanks,

Will

[v-sathmakuri]

Hi @JasperWilli ,

I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @JasperWilli ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @JasperWilli ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[bhanu_gautam · Accepted solution]

@JasperWilli 

Load Data from SharePoint List: First, ensure that your SharePoint list is loaded into Power BI. You can do this by using the "Get Data" feature in Power BI and selecting SharePoint Online List as your data source.

Data Modeling: Once your data is loaded, you need to model it correctly. Ensure that your data has relationships defined between Projects, Goals, and Pillars. You can do this in the "Model" view in Power BI.

Create Measures: You will need to create measures to calculate the progress at each level. For example, you can create a measure to calculate the average percent completed for each Goal and then another measure to calculate the average percent completed for each Pillar.

Here is an example of how you can create these measures using DAX:

DAX
AveragePercentCompletedGoal =
AVERAGEX(
FILTER(
Projects,
Projects[Goal] = EARLIER(Projects[Goal])
),
Projects[PercentCompleted]
)

AveragePercentCompletedPillar =
AVERAGEX(
FILTER(
Projects,
Projects[Pillar] = EARLIER(Projects[Pillar])
),
[AveragePercentCompletedGoal]
)

After creating the necessary measures, you can visualize the data. Use a bar chart, pie chart, or any other visual that suits your needs to display the progress at the Pillar level. Drag the Pillar field to the axis and the AveragePercentCompletedPillar measure to the values.