Forum Discussion
Matrix like pivot table: calculated average value to all levels isn't correct. Why?
I have a semi complex data model which is returned from a rest service. There are Task which have a Status, to each Status is assigned a completition in percent. Tasks have a N to M relationship with Objectives.
Data Model
The goal is to calculate the completition of an Objective based on the completition of the related Tasks.
I used a Matrix which should mime an Excel pivot table but for some reasons it doesn't.
First I added the Objectives and Tasks to the rows of the Percent of the Progress to the values, but it didn't work because Power BI didn't know the context. So I added a calculated column (Percent) to the Tasks and added this new "Percent" to the values of the matrix but it still doesn't work correctly.
Matrix
The "Percent" is correct in the Tasks but as soon as it moves one level up to the "LinkedIssue" table it changes to 0.24, no matter which Tasks are related. The calculated complentition percent of the Objectives is always 0.24 which is most probaly the average percent off all the Tasks.
So, how can be a pivot table simulated in power BI with a matrix and have all the "upper levels" with the correct "summarized" values?
1 Reply
- lbendlin
Super User
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
