Calculated Field in PowerPivot

Question

I have tried researching this question, but have had issues understanding DAX.

I am using PowerPivot instead of a normal pivot table so that I can use the distinct count feature. I would like to create what in a classic pivot table would equate to a calculated field. I put together an example in the picture below.

In the value section of the pivot table, I have a distinct count of the sales of red hats (column B). To the right also in the value section, I have the count of total hat sales (all colors) (column C). I would like to add another column that displays the red hat sales as a percent of the total hat sales - The solution that I am looking for would divide the 'Distinct Count of Red Hat Sales' over the 'Count of Total Sales' using a field or measure in PowerPivot (Essentially what I have manually entered in column D).

I believe that I am doing to have to use DAX to create a measure?

I am sure that this has probably been answered before, but I have had trouble finding exactly what I am asking. Any help would be greatly appreciated.

Answer 1

You need to create a DAX measure with the following formula:

= DIVIDE( [Count of Red Hats], [Count of Total Hats])

This function ignores division by zero errors. If such errors happen, the function by default displays blanks, but you can choose to display something else (DIVIDE function has third optional parameter).