SOLVED

Pivot Table - Add Column to Calculate Percent Sold

Copper Contributor

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

@Ahoyt145 

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

image.png

Please check in attached.

@Ahoyt145 

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

See the attached version.

@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?

Capture.JPG

This is what I get using your DAX measureCapture2.JPG

 

best response confirmed by Ahoyt145 (Copper Contributor)
Solution

@Ahoyt145 

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

image.png

@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!

@Ahoyt145 , you are welcome.

To sort: select More sort options

image.png

and here

image.png

zip codes

If manually you may slicers on fields from source data and filter by them

image.png

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.

Beautiful! Thank you again

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

1 best response

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

@Ahoyt145 

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

image.png

View solution in original post