How to show progress of success rate over time

[jereaallikko]

Hi all,

I am trying to make a visual to show a progress of successful testing rate over time (for instance with column/line chart, where date is as an axis). 

I have created a following DAX measure to show the percentage of passed tests compared to all:

Passed % = DIVIDE(CALCULATE([Rows];FILTER('Table';'Table'[Passed Tests] > 0));[Rows];0)

And it works in a gauge visual like this

I would like to compare that percentage to yesterday's/last week's/last month's number. In my table I have columns containing timestamps (f.ex. last passed, created, modified) if those are needed to make it work. 

Hopefully somebody can provide me a solution to my problem. Don't hesitate to ask more information if it is needed!

Thanks

Jere Aallikko

[amitchandak]

@jereaallikko , To Get data of yesterday Today , last week, Last month you refer my blogs. You need a date table and date without a timestamp .

If they timestamp create a date like

New last passed = [last passed]

Now join all date with date table. Only one would be active join you can use userelation in a measure to use other.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

In Case you srill not able to get it.Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jereaallikko]

Hi @amitchandak 

Thank you for your answer. Unfortunately, I still was not able to get it done, maybe I missed some steps in you blogs.

Unfortunately, I cannot share the original data, as it is fetched through REST API and access would be needed. I have created a similar sample data as a pbix file with some explination what is needed.

The data is rough and made very simple, but hopefully it helps.

https://drive.google.com/file/d/15UCndY84dtsExd3cXKjZdYVhL9K7TbHU/view?usp=sharing

I cannot share the file directly here without being a superuser etc. so here is the link to it.

If you can solve the problem on the pbix file, could you please forward it back to me. 

Thanks for your effort,

Jere

[v-deddai1-msft]

Hi @jereaallikko ,

Would you please show us more details for your expected output?  compare that percentage to yesterday's  means compare the count of passed test up to yesterday with  the count of passed test up to selected day?

You can refer to the  PREVIOUSDAY, PREVIOUSMONTH function https://docs.microsoft.com/zh-cn/dax/previousday-function-dax. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[jereaallikko]

Hi @v-deddai1-msft 

The problem is difficult to explain, but the aim is to show the progress of the percentage number over time in a chart, where axis is a date hierarchy showing every day. Not only dates of Last Passed.

To illustrate, here is an example with 20 test cases total, 5 of them passed, the passed percentage is 25%:

10.09.2020, 20 test cases, 5 passed, passed percentage 25%

11.09.2020, amount of test cases remains the same, 1 new passed =6, passed percentage 30%

12.09.2020, 3 new test cases added=23, same amount passed =6, passed percentage 26,09%

13.09.2020, all remains the same, passed percentage 26,09%

14.09.2020, amount of test cases are same=23, 3 tests passed=9, passed percentage 39,13% 

And so on...

So the chart should show the development of the passed percentages over time.

Hopefully this opens up the issue a bit more. As mentioned, it is a bit difficult to explain, but trying my best. Feel free to ask more if something is still unclear.

Thanks,

Jere

[v-deddai1-msft]

Hi @jereaallikko ,

I suggest you use line chart to show progress of the percentage number over time.

You can create the following measure based on your sample data:

Measure = CALCULATE(DIVIDE(CALCULATE(COUNTROWS(Table1),Table1[Status] = "Passed"),COUNTROWS(Table1)),FILTER(ALL(Table1),Table1[Last test Run]<=MAX('Calendar'[Date])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[jereaallikko]

Hi @v-deddai1-msft 

It worked thanks. But I faced a problem with applying slicers to the graph. The measure remains the same.

 

How can I make the measure graph change according the slicer?

BR,

Jere

[v-deddai1-msft · Accepted solution]

Hi @jereaallikko ,

Hi @jereaallikko ,

Why you need to filter your chart by test name?  You can use the following measure to apply slicer to measure:

Measure = CALCULATE(DIVIDE(CALCULATE(COUNTROWS(Table1),Table1[Status] = "Passed"),COUNTROWS(Table1)),FILTER(ALLSELECTED(Table1),Table1[Last test Run]<=MAX('Calendar'[Date])))

Passed % = DIVIDE(CALCULATE([Rows],FILTER('Table1','Table1'[Passed Tests] > 0)),CALCULATE([Rows],ALL(Table1)),0)

And it will display useless information:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[jereaallikko]

Hi @v-deddai1-msft 

Sorry to bother you again, but how should I modify the DAX if I want to take other "Status" to the calculation as well?

I would like to show the % value when Status="Passed" or "N/A". Can you help me how to modify the expression? So exactly the same calculation, but when Status is Passed or N/A.

Thanks,

Jere

[jereaallikko]

Hi @v-deddai1-msft 

Now everything works. Thank you very much!

Br,

Jere