0

I am trying to create a measure which will display the percentage of Grand total for each row.

Below images might give an overview about the progress I have made and the actual requirement.

Note: Below is the dummy data which I have pasted in excel to describe the problem. Actual solution needs to be given using power-pivot.

Progress: Current Report

Report gives an overview for the learning curve of each employee in the organization. If the employee has completed the particular course, say Subject 1 then value will be 1 else blank. I have created a measure 'Course Completed' to populate the field. Using the pivot table options, got the grandtotal for rows. I also need a column in the report which will give the '% of grandtotal'. For which I have created a new measure as '% of Course Completed', formula of this measure is same as 'Course Completed' but I used the 'show value as '% of Grandtotal

Actual requirement: Actual Requirement

I do not need '% of Grandtotal' for individual course. Course is a filter for the reports. So, No of courses may change based on the filter. Manually hiding the column is not a solution. Any Help?

Pranshu Mishra
  • 1
  • 4

1 Answers1

0

Solution :

Create a Calculated Column (and not Calculated Field OR Member) as follows.

Total % of Course Completed =
      ([Subject 1 Course Completed]  + [Subject 2 Course Completed]) * 100 / 2

Where left hand side is name of the newly created Calculated Column and right side is the formula for calculated column. You need to update columns names as per your data model.

Also, I have assumed number of course as fixed to be 2, change this accordingly.

Calculated Column vs Measure (Calculated Field) :

Calculated columns are suitable when you operate on individual row, it is as if you are adding column to your existing table. Measures are far more powerful and work on Current Context.

Abhijeet Nagre
  • 876
  • 1
  • 11
  • 21