 SOLVED

# Pivot Table - Add Column to Calculate Percent Sold

I have a spreadsheet with all of my companies accounts in Sales Force from the past year, both sold and unsold organized by zip code.
The main purpose of what I'm using this for is to help better direct our marketing efforts. I want to see the number of sold accounts, unsold accounts, total accounts and percentage sold in each zip code. The actual value of the sale is not important for this.

Example: (Did my best to make up it line up)

Zip code Sold Unsold Total %Sold

75022 ___4 ___ 8 ___ 12 _ 33.3%
75028 ___7 ___24 ___31 _ 22.5%

When I create my pivot table, I'm able to see exactly the above, except for the last column. How can I create a column to show % Sold?

I'm assuming that my data is just set up incorrectly but I've been trying all day and cannot figure out how to organize it properly so that when I create the pivot table, I'm able to select the options I need to get it to display properly.

I know work arounds to get the percentages outside of the pivot table but I need them within the pivot table so that I can sort and view properly.

I've seen people suggest using DAX measures which seems like what I need to do, but every example I've found is to calculate the average values of a list of numbers. Mine is more of a  true/false (sold/unsold) argument and I haven't been able to translate their walk throughs to my data.

I've attached a small sample of the data and the pivot table I created. Any help is very much appreciated!

8 Replies

# Re: Pivot Table - Add Column to Calculate Percent Sold

DAX measure could be

``````Sold, %:=
VAR total=COUNTROWS(Range)
VAR totalSold=CALCULATE(COUNTROWS(Range),Range[Sale Status]="Sold")
RETURN DIVIDE(totalSold, total, 0)
``````

which returns # Re: Pivot Table - Add Column to Calculate Percent Sold

If you're willing to do away with the grand totals, you can use a calculated item =Sold/(Sold+Unsold).

See the attached version.

# Re: Pivot Table - Add Column to Calculate Percent Sold

@Sergei Baklan Thank you so much, this is exactly what I'm looking for. Is there a way to organize the table like this though? This is what I get using your DAX measure best response confirmed by Ahoyt145 (New Contributor)
Solution

# Re: Pivot Table - Add Column to Calculate Percent Sold

Instead of using implicit measures you may add explicit DAX measures as

``````Sold:=CALCULATE(COUNTROWS(Range),Range[Sale Status]="Sold")

Unsold:=CALCULATE(COUNTROWS(Range),Range[Sale Status]="Unsold")

Total:=[Sold] + [Unsold]``````

and use them in PivotTable # Re: Pivot Table - Add Column to Calculate Percent Sold

@Sergei Baklan You are a genius, sir. Thank you!
One last question (maybe 2). I have very limited experience with creating pivot tables, so I apologize as this is all new to me.
How can I now sort this information? For example, I want to sort by Sold, % largest to smallest. Then I'd also like to eliminate any zip codes that have less than 10 total accounts.
Thanks again!

# Re: Pivot Table - Add Column to Calculate Percent Sold

@Ahoyt145 , you are welcome.

To sort: select More sort options and here zip codes

If manually you may slicers on fields from source data and filter by them If by parameters we shall to define it somewhere in the grid (e.g. in parameters table), add it to data model and modify measures using such parameters.

# Re: Pivot Table - Add Column to Calculate Percent Sold

Beautiful! Thank you again

# Re: Pivot Table - Add Column to Calculate Percent Sold

@Ahoyt145 , step by step... You are welcome