Creating Percent value for a column that contains Y/N/NA.

[ESC10]

Would like to calculate a Percent for each of the Y's, N's  and the NA's.

Thanks.

[ESC10]

Would like to sum rows and calculate a percent for each of the 3 possible choices.  Yes, No, N/A.

Rookie alert.

[Anonymous · Accepted solution]

There are about a million ways to do this, Even the visualizations can do a good deal of this for you. Using basic DAX:

Yes = CALCULATE(count(Sheet1[Column1]),Sheet1[Column1] = "yes") 

What your are doing here is telling DAX to count column1 after it applies the filter = yes (this is what the Calculate Function does), Repeat this for all your options and you will get the counts of your options.

One more set of mesures will get you the percentage. The total has no filter so it counts all the items. 

Total = count(Sheet1[Column1])

%Yes = [Yes]/[Total]

Just Mesures

this is not really a useful way of doing it, normally you would combine the mesures with the visualization so that you could take advantage of the filtering that the visualizations perform on the data for you (as part of the presentation). This allows the DAX to be more flexible then the SQL backend using the power m (which I am actually more comfortable with). 
 to make it a bit more practical you can use the ALL() funtion to make the total count function unaffected by filters and just have the Mesure count all the values the visualization filter provides it (for the sake of contuinuity I will keep the %Yes Mesure and add the Yes% Mesure so you can compre how the All() affects the Mesures in the visual.  When looking at these two keep in mind that both use the altered Total Mesure.

Total = countx(all(Sheet1),Sheet1[Column1])

Yes% = count(Sheet1[Column1])/[Total]

Now in your visualization a single column will calculate all three using the filter of the visualization Column added creating a filter for the data, Yes% has the ALL() to clear the flters

Note that without the ALL on the total, the visualization filter would make the %Yes column give you: < #of yes / the number from the filter in current row >,  so if there are 7 yes answers total this is what %yes will have as the numerator, and the number in "count of column1" will be the denominator so 7/4 =1.75 or 175%. Because we defined total as the count of column1 Yes% is col1/col1 which will always be 100%.Filter messes it all up!

Hope this helps.