Help for DAX writing to get the below shown output

[suryaburaboyina]

HI,

I am facing an issue to get the below required output for the column YES% where we need to calculate it based on the rowwise calculation like ones(in green color)/(Blanks(in white color)+ Zeros(in red color)).

please help.

Regards,

Surya

to achive yes % column

[Cmcmahan]

Sure. How is your table set up?  I'm going to make guesses at the columns, but if you could provide applicable structure, that'd be great.

You should be able to set up your last column as a measure similar to

Yes% = COUNTAX('Table', 'Table'[response] = 1) / COUNTAX('Table', 'Table'[response] <> 1)

[suryaburaboyina]

Thanks for the response Cmcmahan,

But i am not able to get the required output, below are the formulas i am using to get the output in matrix table.

columns is Projects and Rows is Dates.

3 measures i am usuing to calculate Yes, they are as

1)

Yes% = CALCULATE(COUNT('projects'[Status])+0,FILTER('projects','projects'[Status]="Yes"))/CALCULATE(COUNT('projects'[Status])+0,FILTER('projects','projects'[Status]<>""))

2) 

No% = CALCULATE(COUNT('projects'[Status])+0,FILTER('projects','projects'[Status]="NO"))/CALCULATE(COUNT('projects'[Status])+0,FILTER('projects','projects'[Status]<>""))

 3) This depends on Yes% and No%:

Yes(%) = if([Yes%]/([No%]+CALCULATE(COUNT('projects'[Status]),(FILTER(projects,projects[Status] = "")))),CALCULATE(COUNT('projects'[Status]),FILTER('projects','projects'[Status]="Yes")),0)

Depending on the 3 rd measure the result should vary for the calculation of Yes% as i have shown in the chart yesterday.. Below is how my chart looks now.

Thanks,

Surya 

[Cmcmahan]

Your original yes and no % calculations look like they should work.  Yes% seems to get the count of yes over the total of statuses that are NOT blank.  Same with no%.

I'm going to focus in on your 3rd measure, since that seems to be the weird one.  Lets say in a row, you have 2 "Yes" values, 1 "No" value, and 1 Blank to walk through this.

//Your original 3rd measure
Yes(%) = if( [Yes%]/([No%] + CALCULATE( COUNT('projects'[Status]),
                                        FILTER(projects,projects[Status] = ""))
            ),
              CALCULATE( COUNT('projects'[Status]), FILTER('projects','projects'[Status]="Yes")),
              0)

So if I replace values in here with our pretend 2Y/1N/1B setup (color coding so it's clear where we got the numbers), we get an equation that looks like this:

Yes(%) hypothetical = IF( (0.66 / (0.33 + 1)), 2, 0)

What you're actually calculating here is "If .66 divided by 1.33 exists/is not zero, then count Yes, else 0."  Clearly, you don't want to check if 0.5 exists to determine what you're doing here.  

You'll notice some weird stuff about this math in general.  Firstly, you're taking a percentage of yes results, and dividing it by another percentage. But then you're adding the count of blanks to that percentage?  In addition, this is always going to be either 0 (if no Yes), undefined (if all Yes), or a number greater than 1 if there's a mix of Yes/other.  

Going back to your original post, if all you need is the final row calculation, and you only have options of "Yes", "No", and BLANK(), you can do it easily in one measure, like this:

Yes(%) Total = DIVIDE( CALCULATE(COUNT('projects'[Status]), FILTER('projects', 'projects'[Status] = "Yes")),  
                       CALCULATE(COUNT('projects'[Status]), FILTER('projects', 'projects'[Status] <> "Yes")),
                       1)

And you should be able to just stick that right in your table!  The reason I'm using DIVIDE instead of the / operator is because with DIVIDE, I can specify a result in case of division by zero, which can happen if all the projects are "Yes" and there aren't any project to divide by.  In that case, I'm assuming you want to display 100%.