Jun 14 2021 07:30 AM
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!
Jun 14 2021 07:47 AM
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.
Jun 14 2021 07:49 AM
If you're willing to do away with the grand totals, you can use a calculated item =Sold/(Sold+Unsold).
See the attached version.
Jun 14 2021 03:20 PM
@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
Jun 15 2021 03:43 AM
SolutionInstead 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
Jun 15 2021 09:16 AM
Jun 15 2021 10:44 AM
@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.
Jun 15 2021 01:59 PM
Jun 15 2021 02:46 PM
@Ahoyt145 , step by step... You are welcome
Jun 15 2021 03:43 AM
SolutionInstead 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