cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bryanc78 Regular Visitor
Regular Visitor

Dax help - measure to show % of passed items by category

I have a clustered stacked chart in excel I use to trend test improvements over the last few months.  For example, the % of question 1 being passed will show for april, may and june.  Then the same for question 2 and so on.

 

PBI doesn't support that but it looks like their stacked % chart will get me close.  Problem is, % is for all of the questions and not just for one category at a time

 

Below is how my columns look in PBI and I would like the stacked % bar chart to show that question 1 is 75% as that is the percentage of the question being passed.  The next section of the bar would be for question 2 and that would show 50%.  Then I can have a 1 column for each month and I can compare improvements that way.

 

This possible?

image.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell New Contributor
New Contributor

Re: Dax help - measure to show % of passed items by category

That is caused by the fact that if either argument to the divide function returns a blank, the result of the divide will be blank. One fix for ths is to check if the count of all scores is greater than 0 then add a hard 0 to the count of passed scores.

eg

Pass % = 
VAR _allScoresCnt = CALCULATE(COUNTROWS(Table1),ALL(Table1[Score]))
VAR _passCnt = CALCULATE(COUNTROWS(Table1), Table1[Score] = "Pass") + IF(_allScoresCnt > 0,0)
return DIVIDE(_passCnt, _allScoresCnt)
5 REPLIES 5
d_gosbell New Contributor
New Contributor

Re: Dax help - measure to show % of passed items by category

You should be able to do this with a measure like the following

Pass % = 
VAR _passCnt = CALCULATE(COUNTROWS(Table1), Table1[Score] = "Pass")
VAR _allScoresCnt = CALCULATE(COUNTROWS(Table1),ALL(Table1[Score]))
return DIVIDE(_passCnt, _allScoresCnt)

Then simply create a normal stacked column chart and put this measure on the values and the questions on the Axis

 


@bryanc78 wrote:

PBI doesn't support that but it looks like their stacked % chart will get me close. 


I don't really understand what you are trying to achieve. If you can post an image of the output maybe the same chart exists in Power BI but just has a different name.

bryanc78 Regular Visitor
Regular Visitor

Re: Dax help - measure to show % of passed items by category

This is close but on one of my questions, I don't have any that have passed. Instead of showing 0%, the question is not even showing at all
d_gosbell New Contributor
New Contributor

Re: Dax help - measure to show % of passed items by category

That is caused by the fact that if either argument to the divide function returns a blank, the result of the divide will be blank. One fix for ths is to check if the count of all scores is greater than 0 then add a hard 0 to the count of passed scores.

eg

Pass % = 
VAR _allScoresCnt = CALCULATE(COUNTROWS(Table1),ALL(Table1[Score]))
VAR _passCnt = CALCULATE(COUNTROWS(Table1), Table1[Score] = "Pass") + IF(_allScoresCnt > 0,0)
return DIVIDE(_passCnt, _allScoresCnt)
bryanc78 Regular Visitor
Regular Visitor

Re: Dax help - measure to show % of passed items by category

That worked, thank you.

Do you know if any resources that would good to start learning DAX?
d_gosbell New Contributor
New Contributor

Re: Dax help - measure to show % of passed items by category

My friends over at sqlbi.com have a stack of great resources. They wrote a book called "The Definitive Guide to DAX" and the also have videos and run training courses and have a number of articles on their site. And there is a list of a number of great DAX resources here: https://social.technet.microsoft.com/wiki/contents/articles/1088.dax-resource-center.aspx