cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trdoan Member
Member

Percentage change compared to a threshold and Percentage change MoM

Hi everone,

 

I have 2 tables:

 

1. "Material Groups Data" table:

GroupModelVendorGroup-EngineAverage Volume ThresholdAverage TAT Threshold
HFCU300AHFCU-300436.4
FMU500AFMU-500436.4
HFCU500AHFCU-500436.4
CASE300BCASE-300354
LINER Y300CLINER Y-300455
CASEGG YP6DCASEGG Y-P66106

 

2. "Full" table:

NameOrder QuantityGroup-EngineModelFinal Run TimeDate
A1HFCU-30033June 18, 2018
B1HFCU-30019January 22, 2018
B15HFCU-50015January 29, 2018
C1FMU-30073January 29, 2018
C6FMU-50051July 7, 2018
G48FMU-30039June 22, 2018
J1LINER Y-30027July 9, 2018
H12CASE-W523March 5, 2018
U9CASEGG Y-W519February 20, 2018
K1CASEGG Y-P618January 29, 2018
A22HFCU-P634August 31, 2018
A28FMU-P634April 10, 2018
S1FMU-50050April 3, 2018
M1LINER Y-30047January 3, 2018
A1LINER Y-500 January 3, 2018
B26FMU-300 April 3, 2018
C3FMU-500 January 3, 2018
F1FMU-500 January 3, 2018
E1LINER Y-P6 January 3, 2018
G1LINER Y-P6 May 3, 2018
G7CASE-P6 January 3, 2018
C1HFCU-300 January 3, 2018
A8HFCU-300 January 3, 2018
S1HFCU-500 January 3, 2018
D12HFCU-500205January 3, 2018
D1HFCU-500204January 3, 2018
D18HFCU-500204May 29, 2018
D69LINER Y-P614April 18, 2018
D24LINER Y-P614February 20, 2018
F1FMU-50014February 20, 2018
G23FMU-300 February 20, 2018
G25LINER Y-50098February 20, 2018
B1LINER Y-P656February 20, 2018
N1FMU-P644March 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!

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Percentage change compared to a threshold and Percentage change MoM

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

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

Re: Percentage change compared to a threshold and Percentage change MoM

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

Community Support Team
Community Support Team

Re: Percentage change compared to a threshold and Percentage change MoM

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:

1.PNG

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

 

Regards,

Daniel He

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