cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
suryaburaboyina Frequent Visitor
Frequent Visitor

Help for DAX writing to get the below shown output

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

 

yes%.pngto achive yes % column

3 REPLIES 3
Super User
Super User

Re: Help for DAX writing to get the below shown output

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)
Highlighted
suryaburaboyina Frequent Visitor
Frequent Visitor

Re: Help for DAX writing to get the below shown output

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.

 

yes%.png

Thanks,

Surya 

Super User
Super User

Re: Help for DAX writing to get the below shown output

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%.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 97 members 1,527 guests
Please welcome our newest community members: