This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have a pivot table with data grouped by weeks. There are two fields that I need to know what percentage one field is of the other. My calculated field is returning the wrong answer. For the calculated field I am using this formula: ='Premium Widgets'/'All Widgets'
| Row Labels | Widgets Sold | Premium Sold | % Premium | Expected Result |
|---|---|---|---|---|
| 1/1/2023 - 1/7/2023 | 344 | 152 | 68% | 44% |
| Baton Rouge | 17 | 9 | 75% | 53% |
| Connecticut | 1 | 0 | 0% | 0% |
| Fayetteville | 3 | 1 | 66% | 33% |
| Gainesville | 1 | 1 | 100% | 100% |
| Hampton Roads | 13 | 6 | 74% | 46% |
| Kansas | 113 | 50 | 67% | 44% |
| Lafayette | 13 | 8 | 84% | 62% |
| Las Vegas | 7 | 6 | 96% | 86% |
| Macon | 4 | 1 | 56% | 25% |
| New Orleans | 40 | 5 | 31% | 13% |
| Northern Virginia | 3 | 2 | 89% | 67% |
| Oklahoma City | 11 | 8 | 91% | |
| Omaha | 15 | 8 | 74% | |
| Orange County | 28 | 15 | 72% | |
| Pensacola/Ft. Walton Beach | 10 | 3 | 44% | |
| Phoenix | 28 | 11 | 66% | |
| Rhode Island | 7 | 5 | 87% | |
| Roanoke | 1 | 1 | 100% | |
| San Diego | 22 | 7 | 51% | |
| Santa Barbara | 1 | 1 | 100% | |
| Tucson | 4 | 2 | 57% | |
| Tulsa | 2 | 2 | 100% | |
| 1/8/2023 - 1/14/2023 | 468 | 207 | 69% | |
| Baton Rouge | 24 | 11 | 70% | |
| Connecticut | 8 | 4 | 80% | |
| Fayetteville | 3 | 2 | 88% |
Here is a link to a simplified version Demo Data.xlsx
Thanks in advance for any help you can offer!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
The calculated field computes (sum of premium widgets)/(sum of all widgets).
For example for Fayetteville, January 1 to 7:
Sum of premium widgets = 402
Sum of all widgets = 609
402/609 = 66%
The calculated field computes (sum of premium widgets)/(sum of all widgets).
For example for Fayetteville, January 1 to 7:
Sum of premium widgets = 402
Sum of all widgets = 609
402/609 = 66%
OK, that helped. The numbers in the columns are only codes from the source data. So I created helper columns to replace those codes with "1" as appropriate and now the math works and the percentages are right.
Thanks!
Hi,
Load the data to the Data Model and write this measure
Measure1 = divide(sum(Data[Premium sold]),sum(Data[Widgets sold]))
Hope this helps.