Forum Discussion
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!
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
8 Replies
If you're willing to do away with the grand totals, you can use a calculated item =Sold/(Sold+Unsold).
See the attached version.
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
Please check in attached.
SergeiBaklan 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
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
