Calculation of Percentages in Pivot with Distinct Count

Question

Hello,

How does one calculate percentages in a Pivot Table that uses the Distinct Count (Power Pivot)?

In the sample above, I would like to calculate the MCMP, Not On Time and On Time percentages of the Grand Total for each month. I have done this previously with typical Pivot Charts but not with Power Pivot (which I used to perform the Distinct Count).

Thanks in advance for any assistance

Answer 1

Hi @Flinn, Randal J ,

-------- Update --------

• First, I create 3 Measures to get the distinct count for each column each month.

For MCMP, I use this Measure MCMP Count=CALCULATE(DISTINCTCOUNT(Table1[MCMP]),FILTER(Table1,not ISBLANK([MCMP])))
For Not On Time, I use this Measure Not On Time Count=CALCULATE(DISTINCTCOUNT(Table1[Not On Time]),FILTER(Table1,not ISBLANK([Not On Time])))
For On Time, I use the Measure On Time Count=CALCULATE(DISTINCTCOUNT(Table1[On Time]),FILTER(Table1,not ISBLANK([On Time])))

• Then I create other 3 Measures to get the percentage in each month for each column.

For MCMP, Percentage 1=DIVIDE([MCMP Count],[MCMP Count]+[Not On Time Count]+[On Time Count])
For Not On Time, Percentage 2=DIVIDE([Not On Time Count],[MCMP Count]+[Not On Time Count]+[On Time Count])
For On Time, Percentage 3=DIVIDE([On Time Count],[MCMP Count]+[Not On Time Count]+[On Time Count])

Any misunderstandings, you could post back.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in [our documentation][3] to enable e-mail notifications if you want to receive the related email notification for this thread.

---