Create a calculated field in PowerPivot

Open the Excel workbook which we used in our previous post on this topic. Olympics Excel Work book.

Select PowerPivot -> Manage

PowerPivot Window will open, If Calculation area not visible below table data as shown in below screen. Click Calculation Area button in Top Right Corner.



In the Calculation Area, select the cell directly below the Edition column. From the ribbon, select AutoSum > Distinct Count, as shown in the following screen.


Power Pivot automatically created the following DAX formula: Distinct Count of Edition:=DISTINCTCOUNT([Edition])

Additional calculations in AutoSum are just as easy, such as Sum, Average, Min, Max, and others.

Let’s calculate the percentage of all medals. In the formula bar, type the following DAX formula. IntelliSense provides available commands based on what you type, and you can press Tab to select the highlighted IntelliSense option. Percentage of All Medals:=[Count of Medal]/CALCULATE([Count of Medal],ALL(Medals))


When we return to Excel Sheet we can find these fields available in Medal Table. Shown in the right side of the screen above.

Let’s create a PivotTable from Medal Table. Our Pivot Table will be designed as below screen.


I will come up with more details inn my upcoming posts. Till then stay tuned, keep Learning and keep practicing.