SOLVED

Percentage of count inside a Pivot table

Copper Contributor

Is there a way to get a "running" % of completed visits inside a pivot table based on the count. 

 

Example 

Justin_Yost_0-1591583862767.png

 

6 Replies
best response confirmed by Justin_Yost (Copper Contributor)
Solution

@Justin_Yost Assuming you are on a Windows system (i.e. not Mac), make sure that "Add this data to the Data Model" is checked (see picture, bottom left).

Screenshot 2020-06-08 at 06.33.37.png

Then create the PT as in your example, but leave out the last column. Then add a 'Measure' to the table in the 

Screenshot 2020-06-08 at 06.37.48.png

Name the measure and enter the DAX formula required

Screenshot 2020-06-08 at 06.39.28.png

Add the Measure field to the PT and format it as a percentage. Then it should look like this:

Screenshot 2020-06-08 at 06.42.41.png

The attached file contains the example shown above.

 

THANK YOU THANK YOU THANK YOU@Riny_van_Eekelen .

 

It took me a little bit, but that worked! 

@Justin_Yost Great! Glad you could figure it out.

@Riny_van_Eekelen I love this concept, and I learned something new!  Thank you for this post. 

 

I am trying to leverage this functionality with my data set, but I am struggling to set it up in a way to get what I need from it. Hoping you can assist.

 

I am looking for a compliance rate for teammates within their specific Default Group. My data is pulled so that I have the Teammate and Default Group with their weekly timesheet hours submission. The goal is to be at least 40 hours per week.

 

Data set with hours subittedData set with hours subitted

 

Then, we calculate what the compliance rate is based off of the number of teammates that submitted 40+ hours/# of Teammates in the Group.

 

Compliance rate percentageCompliance rate percentage

 

Instead of using a manual calculation, I was hoping to use this method. However, I cannot figure out how to get the data setup and create a usable formula. Any assistance would be greatly appreciated!

@MandiA15 Not sure I follow, and since the last post a lot has happened to Excel. Perhaps the Data Model / DAX measures route is not needed in your case.

 

What Excel version are you using? What platform? Can you share a file (without sensitive information) via Onedrive or similar?

@Riny_van_Eekelen Thank you for your quick response!

 

To answer your questions:

  • What Excel version are you using?
    • Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20182) 64-bit
  • What platform? 
    • Microsoft Windows 11 Enterprise
  • Can you share a file (without sensitive information) via Onedrive or similar?
    • I have sent you a sample file.
1 best response

Accepted Solutions
best response confirmed by Justin_Yost (Copper Contributor)
Solution

@Justin_Yost Assuming you are on a Windows system (i.e. not Mac), make sure that "Add this data to the Data Model" is checked (see picture, bottom left).

Screenshot 2020-06-08 at 06.33.37.png

Then create the PT as in your example, but leave out the last column. Then add a 'Measure' to the table in the 

Screenshot 2020-06-08 at 06.37.48.png

Name the measure and enter the DAX formula required

Screenshot 2020-06-08 at 06.39.28.png

Add the Measure field to the PT and format it as a percentage. Then it should look like this:

Screenshot 2020-06-08 at 06.42.41.png

The attached file contains the example shown above.

 

View solution in original post