DAX in Power BI

Frank Chenyi
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

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

Author

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
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Frank ChenyiBI Developer

Author

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.
You're welcomed Frank
Frank ChenyiBI Developer

Author

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

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial