DAX in Power BI

I'm creating a lines chart in Power BI  that requests a %monthly value for each month for two text variables in a column. The Y-axis is %count of tickets #, the legend is Completion Date (either 'Yes' or "No") and the X-axis is Actual date.

I need to count each month tickets and express as a % of "Yes" i.e Nov2016 count yes/Total nov2016 count for yes &no. And same for "No" . Use this values in the lines chart.

The chart is to cover 1 year stats from Nov2016 to Nov2017 and should be dynamic.

Any idea how to write this dax function.

Thanks
Frank ChenyiBI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abbas abdullaCommented:
Hi,

Can you post dummy data along with what are you trying to achieve?
0
Frank ChenyiBI DeveloperAuthor Commented:
Need assistance with DAX that will generate monthly row totals for Delivered on time=Yes, and Delivered on time=No. Each month %of yes/monthly total(yes+no), then each month %of No/Monthly Total(yes+no). The third variable "Pending is filtered out and not used in the chart.

Using a matrix I get the results but can't convert to line chart because line chart doesn't utilize the %row total like in matrix. What can I do to obtain same values like in the matrix table attached to use for chart.

Thanks
20171221_224021.jpg
20171223_194134.jpg
DummyData.xlsx
0
abbas abdullaCommented:
Hi Frank,

Apply below measures in your BI Model or PowerPivot:

1. Count Yes's
FindYes:=CALCULATE(COUNTROWS(Table1), Table1[Delivered On Time]="Yes")
2. Count No's
FindNo:=CALCULATE(COUNTROWS(Table1), Table1[Delivered On Time]="No")
3. Find Percentage of Yes's
PercentageOfYes:=[FindYes]/([FindNo]+[FindYes])
4. Find Percentage of No's
PercentageOfNo:=[FindNo]/([FindNo]+[FindYes])

I applied above measure into the attached file in power pivot
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Frank ChenyiBI DeveloperAuthor Commented:
Thanks a hundred times. The recommendation worked perfectly and I did resolve my concerns on a timely manner. Once more thank you. Problem solved and line chart came out just as expected.
0
abbas abdullaCommented:
You're welcomed Frank
0
Frank ChenyiBI DeveloperAuthor Commented:
Hi Abbas,
Quick question pls, I'm trying to rearrange this DAX to return 0% (zero) when no value is found from the computation of the percentage of "Yes or Nos". Any ideal, I have tried a few but the outcome is not positive. I greatly appreciate if any suggestions
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.