Pivot Distinct Count and custom layout

Distinct count is not available in Pivot Table by default. However, if you create a pivot table using data in data model, you can get this option. As an additional bonus, you can convert the Pivot to formulas and put the values in any layout you want. See how this is done. Watch this four-minute video.

Contents

1. Regular Pivot Table – 00:09
2. Problem 1 – No Distinct Count – 00:25
3. Problem 2 – Cannot change Pivot Table layout – 00:42
4. Solution – Pivot From Data Model – 00:54
5. Distinct Count – 01:38
6. Custom Pivot Layout – Convert to Formulas – 01:54
7. Cube formulas – 03:14
8. Summary – 03:33

This is an inefficient method

Although what I showed works, it is inefficient. Why? Because the data is residing in TWO places in the same workbook – one copy in Excel sheet and one copy in the data model. That way, it occupies more space than needed. Further, there is an overhead of keeping the data model synchronized when the sheet data changes.

The correct way of doing this is to create a separate blank Excel file. Import the table from the original file directly into the data model. That way, there is only one copy of data and the data model performs much better compression leading to smaller file sizes and faster performance.

See this video for details of how exactly to use the Data Model.

—