Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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
Hi @v-danhe-msft, I was taking random numbers to give a quick example. Sorry for this confusion!
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
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |