Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Would like to calculate a Percent for each of the Y's, N's and the NA's.
Thanks.
Solved! Go to 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]
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
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%.
Hope this helps.
Would like to sum rows and calculate a percent for each of the 3 possible choices. Yes, No, N/A.
Rookie alert.
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]
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
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%.
Hope this helps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |