16. Practice Exercises with Answer Hints
16.6 Module 7: PivotTables
- Pivot of sales by City and Platform. Hint: Rows = City, Columns = Platform, Values = Sum of Amount → total 2,870 on the mini dataset.
- Show each city's sales as a % of the grand total. Hint: Value Field Settings › Show Values As › % of Grand Total.
- Group order dates by month and quarter. Hint: right-click a date › Group › Months, Quarters.
- Count distinct customers per city. Hint: tick Add this data to the Data Model › Value Field Settings › Distinct Count.
- Add a calculated field for 5% commission.
Hint: PivotTable Analyze › Calculations › Fields, Items & Sets › Calculated Field
=Amount*0.05. - Show the top 3 cities by sales. Hint: Row Labels filter › Value Filters › Top 10 › 3 Items.
- Connect one City slicer to two pivots. Hint: right-click slicer › Report Connections.
Ravindra Bagale's Tip
Many students add new rows to the source data and they don't show up even after refreshing the pivot – because the pivot was built on a range, not a Table. Always build the pivot on a Table and use Data › Refresh All. If the pivot shows Count, the column has blanks or text numbers – clean those first.
Ravindra Bagale's Tip – मराठी
बरेच students source data मध्ये नवीन rows add करतात आणि pivot refresh केला तरी त्या दिसत नाहीत – कारण pivot range वर बनवला होता, Table वर नाही. नेहमी Table वर pivot बनवा आणि Data › Refresh All वापरा. Pivot मध्ये Count आला तर column मध्ये blanks किंवा text numbers आहेत – आधी ते clean करा.
Ravindra Bagale's Tip – हिंदी
बहुत से students source data में नई rows जोड़ते हैं और pivot refresh करने पर भी वे नहीं दिखतीं – क्योंकि pivot range पर बना था, Table पर नहीं. हमेशा Table पर pivot बनाओ और Data › Refresh All इस्तेमाल करो. Pivot में Count आए तो column में blanks या text numbers हैं – पहले उन्हें clean करो.