Percentage change compared to a threshold and Percentage change MoM

[trdoan]

Hi everone,

I have 2 tables:

1. "Material Groups Data" table:

Group	Model	Vendor	Group-Engine	Average Volume Threshold	Average TAT Threshold
HFCU	300	A	HFCU-300	4	36.4
FMU	500	A	FMU-500	4	36.4
HFCU	500	A	HFCU-500	4	36.4
CASE	300	B	CASE-300	3	54
LINER Y	300	C	LINER Y-300	4	55
CASEGG Y	P6	D	CASEGG Y-P6	6	106

2. "Full" table:

Name	Order Quantity	Group-EngineModel	Final Run Time	Date
A	1	HFCU-300	33	June 18, 2018
B	1	HFCU-300	19	January 22, 2018
B	15	HFCU-500	15	January 29, 2018
C	1	FMU-300	73	January 29, 2018
C	6	FMU-500	51	July 7, 2018
G	48	FMU-300	39	June 22, 2018
J	1	LINER Y-300	27	July 9, 2018
H	12	CASE-W5	23	March 5, 2018
U	9	CASEGG Y-W5	19	February 20, 2018
K	1	CASEGG Y-P6	18	January 29, 2018
A	22	HFCU-P6	34	August 31, 2018
A	28	FMU-P6	34	April 10, 2018
S	1	FMU-500	50	April 3, 2018
M	1	LINER Y-300	47	January 3, 2018
A	1	LINER Y-500		January 3, 2018
B	26	FMU-300		April 3, 2018
C	3	FMU-500		January 3, 2018
F	1	FMU-500		January 3, 2018
E	1	LINER Y-P6		January 3, 2018
G	1	LINER Y-P6		May 3, 2018
G	7	CASE-P6		January 3, 2018
C	1	HFCU-300		January 3, 2018
A	8	HFCU-300		January 3, 2018
S	1	HFCU-500		January 3, 2018
D	12	HFCU-500	205	January 3, 2018
D	1	HFCU-500	204	January 3, 2018
D	18	HFCU-500	204	May 29, 2018
D	69	LINER Y-P6	14	April 18, 2018
D	24	LINER Y-P6	14	February 20, 2018
F	1	FMU-500	14	February 20, 2018
G	23	FMU-300		February 20, 2018
G	25	LINER Y-500	98	February 20, 2018
B	1	LINER Y-P6	56	February 20, 2018
N	1	FMU-P6	44	March 9, 2018

The 2 tables are linked together by the "Group-Engine" and "Group-EngineModel" columns (many-many relationship)

Can you please show me how to:

1. Create a measure where it can calculate the Percentage Change MoM in Average Final Run Time of only the Groups appearing in the "Material Groups Data" table (using all other data from the "Full" table)?

2. Calculate the Percentage Change between the Average Final Run Time of each group in the "Material Groups Data" table and the corresponding Average TAT Threshold?

Desired Result: When HFCU-500 is selected from a slicer, the measure will be able to show the Percentage Change in Average Final Run Time and the corresponding Average TAT threshold.

Let's assume:

AVG Final Run Time of HFCU-500 = 45.00

Corresponding TAT threshold for HFCU-500 = 36.4

% Change = (45-36.4)/36.4 *100 = 23.63%

I tried the quick measure but it wasn't quite right. Can anyone help me out please? Thank you very much in advance!

[v-danhe-msft]

Hi @trdoan,

Based on my test, I could not figure out the AVG Final Run Time of HFCU-500 = 45.00, I have gotten the average of HFCU-500 to be 157((15+205+204+204)/4), could you please offer me more information about how to calculate the Percentage Change MoM in Average Final Run Time?

Regards,

Daniel He

[trdoan]

Hi @v-danhe-msft,  I was taking random numbers to give a quick example. Sorry for this confusion!

[v-danhe-msft]

Hi @trdoan,

Based on my test, you could refer to below formula:

Measure = var t=SUMMARIZE('Full','Full'[Group-EngineModel],"Avg",AVERAGE(Full[Final Run Time]))
var a= AVERAGEX(t,[Avg])
return a-CALCULATE(SUM('Material Groups Data'[Average TAT Threshold]),FILTER(ALL('Material Groups Data'),'Material Groups Data'[Group-Engine]=SELECTEDVALUE('Full'[Group-EngineModel])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He