# DAX in Power BI

Frank Chenyi used Ask the Experts™
on
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
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

Can you post dummy data along with what are you trying to achieve?
BI Developer

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
Commented:
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
BI Developer

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.

Commented:
You're welcomed Frank
BI Developer

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

Do more with