Comparison tracking failure rates and device services over a time period

[Anonymous]

Hello,

I am looking to create a line and stacked column chart to visualize failure rates on a given device run (percentage of runs failed represented by stacked columns) and when those devices were serviced (service dates represented on a line graph (2 devices serviced this day, 5 devices serviced this day, etc)). 
I am able to get the failure rates to plot how I would like them to look (though the number isn't actually calculating what I would like), but I can't get the line graph to do anything other than show a straight line with a count of total services performed, looking like this

How can I get the dates to show as a proper line graph? I would like it to show that 2 devices were serviced on this date, then 3 devices on this date and so on. 
The bar graph is actually also only counting rows as opposed to a percentage of the total that failed. I would like it to show that 40% of runs failed on this day, 10% on that day and so on.

I am also not opposed to switching which measure gets to be the line and which gets to be the bar. 

[KNP]

Hi @Anonymous, 

It should be relatively straight forward but would need some sample data to work with.

Are you able to post a sample of your data so we can see what it looks like?

Also, the data model relationships may be of use.

Regards,

Kim

[Anonymous]

Hello Kim,

The data being pulled for failure rates would look something like this (though over a much greater time span):

Lot Number	Date	%Failed and Inconclusive
123456	6/30/2021	6
123456	6/30/2021	10
123456	6/30/2021	5
123456	6/30/2021	2
123456	7/1/2021	3
123456	7/1/2021	2
789012	7/1/2021	30
789012	7/1/2021	25
789012	7/1/2021	35
789012	7/1/2021	20
789012	7/2/2021	20
789012	7/2/2021	25
456789	7/2/2021	10
456789	7/2/2021	8
456789	7/2/2021	7
456789	7/3/2021	9
876543	7/3/2021	1
876543	7/3/2021	2
876543	7/3/2021	4
876543	7/3/2021	3
876543	7/3/2021	1

The Device maintenance report looks something like this (though again, over a greater time frame):

Device	Service Date
1	6/25/2021
2	6/25/2021
1	6/30/2021
3	7/2/2021
4	7/2/2021
1	7/4/2021

[KNP]

Is the 'Device' column an ID or count/sum of devices for that date?

Is the '% Failed...' formatted as a number in Power Query?

The reason I ask is, I notice the column and line values say 'Count of...' not sum or avg.

[Anonymous]

The "Device" column is an ID.

The %Failed is formatted as a percentage (Data Type is a decimal number, Format is a percentage). 

[KNP]

Do you have a date dimension in your model?

Can you share an image of your data model with relationships?

At first glance, it looks like you need to change the count to avg or something else to get the correct numbers, although they probably won't be "correct" without a well formed measure.

I will try to throw a sample PBIX file together for you quickly.

[Anonymous]

Yes, I have the Date dimension in the "Shared Axis" field.

Unfortunately, neither the Column values nor the line values give me the option to change out of "Count". The only options it's giving me are either "Count" or "Count (Distinct)"

[KNP]

Sounds like they're not formatted as numeric.

[Anonymous]

I transformed the data to read "Decimal" (sorry, I thought doing it in the table setting would have the same effect as going into Transform Data). I was able to switch the %Failed to an Average, which helped. I'd prefer if it could show the results of each run as opposed to an average for the day, but if it's not something that can be done that's ok. 

As for the relationships, I do not have an active relationship between the %Fail table and the Service table. None of the value on the Service table match whats on the %Fail table, with the exception of dates. 

[KNP]

Yeah that's a bit of a gotcha with the column types.

Average is definitely not the correct aggregation to use but without more info and data I don't know how it is calculated. I really just used it as a placeholder for demo purposes.

If you can explain the calculation and provide the data that makes up the percentage failure calculation I should be able to update my sample PBIX with a proper calculation.

I don't think you need a relationship between those tables but you definitely need the relationship from your date table to both tables.

[Anonymous]

Thank you so much for your help, the .pbix file you uploaded definitely brings me closer to what I'm looking for. 

The %Fail is calculated by seeing what percentage of the total number of samples in the run failed. In a run of 100 samples, 2 of the samples failing would give us our 2% failed rate. I have put together a file of sample data that will hopefully be helpful.

The idea Im trying to graph is whether or not we can see a pattern emerge when there was a high frequency of samples failed and when device servicing occurs. I would like to be able to graph each individual run in its own column (which would lead to very narrow and tight columns, but thats ok), but if it's easier to just have the total percent failed each day, I'd be fine with that too (since lot numbers tend to be used over a couple days anyways). 

https://docs.google.com/spreadsheets/d/1aZcsc7OJcJUzg8Vmr_y8NmPHkpqyZ4Xz/edit?usp=sharing&ouid=11504... 

[KNP]

Have a look at this PBIX file to see if it helps you get the answer you need.

https://drive.google.com/file/d/1-EuyeaxFp1gEAdUNVTzzIDbF7iDv0z29/view?usp=sharing