Re: How to create 3 calculated columns based on a ...

ScarlettBebb · ‎06-07-2023

Hello all,

I'm hoping someone can help with this problem. I am trying to create a calculated column to work out a rolling percentage of passing products vs products tested and convert that into a percentage.

I have an example of my data below in the table;

I would like to create 3 columns in PowerBI from this table. Two of the columns should only count the previous 12months.

New column 1; Count of Bacthes tested over last 12 months

New column 2; Count of Passed batches over last 12 months

New column 3; =(Passed/Tested)*100 in order to work out a rolling 12 month First Pass Approval rate (FPA)

Original data in table below;

Month	Year	Product	Lot	Total Verdict	Attribute(Date)
January	2016	G366	1	0	01-Jan-16
February	2016	G389	1	0	05-Feb-16
March	2016	G366	2	0	05-Mar-16
April	2016	G366	3	1	09-Apr-16
May	2016	G366	4	1	10-May-16
June	2016	G389	2	1	17-Jun-16
July	2016	G389	3	1	06-Jul-16
August	2017	G389	4	1	24-Aug-17
September	2017	G389	5	1	18-Sep-17
October	2017	G389	6	1	24-Oct-17
November	2017	G389	7	1	19-Nov-17
December	2017	G389	8	1	05-Dec-17
January	2017	G366	5	0	06-Jan-17
February	2017	G344	1	0	04-Feb-17
March	2017	G344	2	1	04-Mar-17
April	2017	G344	3	1	03-Apr-17
May	2017	G344	4	1	10-May-17
June	2018	G344	5	1	04-Jun-18
July	2018	G344	6	0	07-Jul-18
August	2018	G366	6	0	02-Aug-18
September	2018	G366	7	0	03-Sep-18
October	2018	G366	8	0	04-Oct-18
November	2018	G366	9	0	01-Nov-18
December	2018	G344	7	0	25-Dec-18

So I should end up with the following table in PowerBI;

Month	Year	Product	Lot	Total Verdict	Attribute(Date)	Count of batches tested (rolling over 12 months)	Count of Passing kits (rolling over 12 months)	FPA (rolling over 12 months) (Number Passed/Tested X100)
January	2016	G366	1	0	01-Jan-16	1	0	0
February	2016	G389	1	0	05-Feb-16	1	0	0
March	2016	G366	2	0	05-Mar-16	2	0	0
April	2016	G366	3	1	09-Apr-16	3	1	33
May	2016	G366	4	1	10-May-16	4	2	50
June	2016	G389	2	1	17-Jun-16	2	1	50
July	2016	G389	3	1	06-Jul-16	3	2	67
August	2017	G389	4	1	24-Aug-17	1	1	100
September	2017	G389	5	1	18-Sep-17	2	2	100
October	2017	G389	6	1	24-Oct-17	3	3	100
November	2017	G389	7	1	19-Nov-17	4	4	100
December	2017	G389	8	1	05-Dec-17	5	5	100
January	2017	G366	5	0	06-Jan-17	5	2	40
February	2017	G344	1	0	04-Feb-17	1	0	0
March	2017	G344	2	1	04-Mar-17	2	1	50
April	2017	G344	3	1	03-Apr-17	3	2	67
May	2017	G344	4	1	10-May-17	4	3	75
June	2018	G344	5	1	04-Jun-18	1	1	100
July	2018	G344	6	0	07-Jul-18	2	1	50
August	2018	G366	6	0	02-Aug-18	4	0	0
September	2018	G366	7	0	03-Sep-18	4	0	0
October	2018	G366	8	0	04-Oct-18	4	0	0
November	2018	G366	9	0	01-Nov-18	5	0	0
December	2018	G344	7	0	25-Dec-18	3	1	33

Thanks,

Scarlett

v-stephen-msft · ‎06-08-2023

Hi @ScarlettBebb ,

Sorry, not very clear. I need more details.

Which column is your [Count of batches tested (rolling over 12 months)] calculated on? [ Count of batches tested (rolling over 12 months)] is calculated based on which column? Do these two columns need to be grouped according to [Product]?

If possible, please give an example of how 4 and 2 are calculated.

Best Regards,

Stephen Tao

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

ScarlettBebb · ‎06-09-2023

Morning Stephen

Thank you for getting back to me on this. I need to create a 3 columns to calcuate the 4 and 2 highlighted in the screenshot. I counted them myself to get the answer in the table shown.

So for example the 4 in column 'Count of batches tested' is worked out by counting the number product 'G366' and lot tested in the last 12months. This should be calcuated from a count of Product column and Lot column and then specifically over a 12 month period.

Then the column 'Count of Passing Kits' is calculated from ther Total Verdict column where 1=PASS and 0=Fail. So counting how many of the 4 batches tested has passed over that 12months gives 2 for that product.

Thanks,

Scarlett