Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to figure out the part (%) of the whole of the population from the below dataset in a pivot table. I know what the answer should be but I need help with the "% of Population".
Calculation for % of Cities:
if(
([Sunny]='Y' and [Rain]='Y')or
([Sunny]='Y' and [Rain]='N') or
([Sunny]='N' and [Rain]='Y') or
([Sunny]='N' and [Rain='N'),
(Count([Cities])/(Count(Total [Cities])))
)
This is where the error is - Calculation for % of Population:
if(
([Sunny]='Y' and [Rain]='Y')or
([Sunny]='Y' and [Rain]='N') or
([Sunny]='N' and [Rain]='Y') or
([Sunny]='N' and [Rain='N'),
(Sum([Cities])/(Sum(Total [Cities])))
)
My answer is 1 instead of the below values.
Sunny | Rain | Cities | Population |
y | n | Dallas | 90 |
y | n | San Antonio | 88 |
n | n | Austin | 82 |
y | y | Laredo | 85 |
n | y | Houston | 80 |
n | n | Lubbock | 82 |
y | y | Ft Worth | 93 |
n | y | El Paso | 84 |
y | y | Arlington | 91 |
n | n | Corpus Christi | 96 |
y | n | Plano | 92 |
n | y | Garland | 86 |
n | n | Irving | 87 |
y | n | Amarillo | 94 |
n | y | Grand Prairie | 76 |
Results | ||||
---|---|---|---|---|
Sunny | Rain | # of Cities | % of Cities | % of Population |
Y | Y | 3 | 20.00% | 20.60% |
Y | N | 4 | 26.67% | 27.87% |
N | Y | 4 | 26.67% | 24.96% |
N | N | 4 | 26.67% | 26.57% |
Hi, Dineen. Try Sum(Population)/Sum(TOTAL Population) for "% of Population"
This measure is in a Pivot Table. Rows are Sunny and Rain
When I try the following
if(
([Sunny]='Y' and [Rain]='Y')or
([Sunny]='Y' and [Rain]='N') or
([Sunny]='N' and [Rain]='Y') or
([Sunny]='N' and [Rain='N'),
(Sum([Population])/(Sum(Total [Population])))
)
or Sum(Population)/Sum(TOTAL Population)
I get 1 as the answer