cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Calculate failure rating in specific periods

I need help calculating failure ratings for specific dates. The query I have calculates the failure rating for each part number taking into account the Total Usage vs Total Warrantied, but does not calculate it for a specific month, year, or other period of time.

The Syntax used is:

% Total Failed =
var partsfailed = SUM(Warranty[Part Qty])
var totalparts = SUM(Usage[USAGE])
var rate = partsfailed/totalparts
return IFERROR(rate,0)
These are the results of the query
lopezeal_0-1638288995927.png

This is data #1

lopezeal_1-1638289099411.png

Data #2

lopezeal_2-1638289187390.png

Relationships

lopezeal_3-1638289225746.png

Date filters in the report

lopezeal_4-1638289314161.png

I think the problem is that the "usage" of the part is not being calculated correctly for the specified period but always takes the "total usage", although it is calculating the correct number of failed parts in that time.

5 REPLIES 5
yingyinr
Community Support
Community Support

Hi @Anonymous ,

Could you please provide some sample data of table Warranty and Usage (exclude sensitive data) and list some special examples with incorrect value to explain it with calculation logic and related screenshot? By the way, the applied field on that two date slicers from the table Warranty and Usage? It is better if you can share a simple sample pbix file with me in order to make troubleshooting and provide you a suitable solution. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

i sent you a private message with my data

Hi @Anonymous ,

I’m sorry to tell you that we could only offer technical support on the Power BI Community for security reason. You can refer the following link to share the file with me.  Also, you can paste your data with Text format directy in your post just like below table. Please exclude the sensitive data before share it with me. Thank you.

How to upload PBI in Community

Col1 Col2 Col3
xx xx xx
xx xx xx

Best Regards

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

Hi @Anonymous ,

After looking at your pbit file, it turns out that you have two date slicers set up in your report and they come from the same table (Warranty table) in the Created On and MFG. on fields. The field that connects the Usage and Warranty tables is party#, thus when using the date slicer, it will filter out the part# that filters the corresponding date period, and the Usage table will only come up with the corresponding part# info. I checked the data and found that usage also shows the Usage value of the filtered part#, but not total usage values. Also, I found a date field in the table Usage, do you want its data to be displayed according to the selected date range?

yingyinr_0-1640771148072.png

yingyinr_1-1640771265611.png

 

Best Regards

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!