70.2K

Verified Solution

Link Copied!

The P$04\_18.$xlsx file contains a single$-$table Data Model on a company's sales and a single blank worksheet. The goal is to create a pivot table that shows the ratio of average revenue per transaction in the Charleston store to average revenue per transaction in all other stores combined, broken down by month:

Create the calculated columns RevenueCharleston:$=[$Revenue$]*($Sales$[$Store$]=$"Charleston"$)$ and RevenueOther:$=[$Revenue$]*($Sales$[$Store$]<>$"Charleston"$).$ Then create the measure Ratio$1$:$=$Average$([$RevenueCharleston$])/$Average$([$RevenueOther$])$ and use it in a pivot table, broken down by month. Round your answers to three decimal places, if necessary.

Modify the Data Model to get the desired ratios. Round your answers to three decimal places, if necessary.

Month Ratio

Jun

Jul

Aug

Total

Transaction Date Store Units Sold Revenue

$16/3/201812$:$00$:$00$ AM Spartenburg $6267\mathrm{.}12$

$26/5/201812$:$00$:$00$ AM Charleston $7365\mathrm{.}89$

$36/5/201812$:$00$:$00$ AM Spartenburg $5232\mathrm{.}2$

$46/5/201812$:$00$:$00$ AM Greenville $147\mathrm{.}22$

$56/5/201812$:$00$:$00$ AM Orangeburg $8364\mathrm{.}72$

$66/6/201812$:$00$:$00$ AM Spartenburg $146\mathrm{.}29$

$76/6/201812$:$00$:$00$ AM Orangeburg $10445\mathrm{.}8$

$86/6/201812$:$00$:$00$ AM Greenville $6284\mathrm{.}4$

$96/6/201812$:$00$:$00$ AM Orangeburg $8372\mathrm{.}72$

$106/7/201812$:$00$:$00$ AM Greenville $7326\mathrm{.}41$

$116/8/201812$:$00$:$00$ AM Orangeburg $9410\mathrm{.}67$

$126/8/201812$:$00$:$00$ AM Charleston $2105\mathrm{.}06$

$136/9/201812$:$00$:$00$ AM Greenville $10475$

$146/9/201812$:$00$:$00$ AM Charleston $9469\mathrm{.}17$

$156/10/201812$:$00$:$00$ AM Orangeburg $146\mathrm{.}55$

$166/10/201812$:$00$:$00$ AM Columbia $10535$

$176/11/201812$:$00$:$00$ AM Spartenburg $6282\mathrm{.}36$

$186/12/201812$:$00$:$00$ AM Spartenburg $9414\mathrm{.}99$

$196/12/201812$:$00$:$00$ AM Columbia $9469\mathrm{.}08$

$206/15/201812$:$00$:$00$ AM Orangeburg $4184\mathrm{.}76$

$216/15/201812$:$00$:$00$ AM Columbia $2106\mathrm{.}84$

$226/15/201812$:$00$:$00$ AM Greenville $9431\mathrm{.}55$

$236/16/201812$:$00$:$00$ AM Greenville $6278\mathrm{.}34$

$246/16/201812$:$00$:$00$ AM Greenville $8381\mathrm{.}36$

$256/17/201812$:$00$:$00$ AM Greenville $10476$

$266/18/201812$:$00$:$00$ AM Orangeburg $10439\mathrm{.}6$

$276/19/201812$:$00$:$00$ AM Orangeburg $8357\mathrm{.}2$

$286/19/201812$:$00$:$00$ AM Columbia $9469\mathrm{.}17$

$296/20/201812$:$00$:$00$ AM Columbia $2106\mathrm{.}44$

$306/20/201812$:$00$:$00$ AM Charleston $4209\mathrm{.}76$

$316/22/201812$:$00$:$00$ AM Greenville $7334\mathrm{.}18$

$326/23/201812$:$00$:$00$ AM Columbia $6326\mathrm{.}46$

$336/23/201812$:$00$:$00$ AM Charleston $7373\mathrm{.}03$

$346/24/201812$:$00$:$00$ AM Columbia $10543\mathrm{.}6$

$356/24/201812$:$00$:$00$ AM Greenville $296\mathrm{.}5$

$366/24/201812$:$00$:$00$ AM Charleston $2105\mathrm{.}42$

$376/25/201812$:$00$:$00$ AM Spartenburg $4185\mathrm{.}24$

$386/26/201812$:$00$:$00$ AM Greenville $10467$

$396/26/201812$:$00$:$00$ AM Greenville $7336\mathrm{.}14$

$406/30/201812$:$00$:$00$ AM Orangeburg $3137\mathrm{.}43$

$416/30/201812$:$00$:$00$ AM Spartenburg $10446\mathrm{.}4$

$426/30/201812$:$00$:$00$ AM Greenville $9414\mathrm{.}99$

$437/1/201812$:$00$:$00$ AM Columbia $10555\mathrm{.}6$

$447/1/201812$:$00$:$00$ AM Greenville $8364\mathrm{.}8$

$457/1/201812$:$00$:$00$ AM Spartenburg $5232\mathrm{.}75$

$467/2/201812$:$00$:$00$ AM Columbia $2108\mathrm{.}96$

$477/4/201812$:$00$:$00$ AM Charleston $3155\mathrm{.}55$

$487/5/201812$:$00$:$00$ AM Spartenburg $8359\mathrm{.}92$

$497/5/201812$:$00$:$00$ AM Spartenburg $7334\mathrm{.}25$

$507/6/201812$:$00$:$00$ AM Columbia $9480\mathrm{.}96$

$517/7/201812$:$00$:$00$ AM Orangeburg $4177\mathrm{.}6$

$527/7/201812$:$00$:$00$ AM Orangeburg $6271\mathrm{.}44$

Answer & Explanation Solved by verified expert

See Answer