Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all
Submitted a previous question to which @Nathaniel_C helped me out (thank again)
I am looking for a way to calculate % diffirence between two values. This could be a % increase or decrease based on the values. My previous question was to calculate % diffirence, but i soon realised i needed % varience instead.
Some background:
- We are running a project for a in house web app we have created. The Web app consists of multiple transaction pages and averages are calculated per page. (sample of data below)
- The aim is to run load tests against the page and use PBI to compare test run averages per page [Transaction Name] so we can see which test runs performed best and by how much, we could then justify code changes from the DEV team on the issues.
- Test runs have their own unique index number.
- The load tests could be done hourly or daily so the dataset will grow quite allot.
>Each test is listed as "Build"
>Each page is listed as "Tranaction Name"
>Each Transaction name have a "Average Response Time"
>Each Build has a Data and Time Column
>I also added a Test Run Index since I was hoping to use it as a method for my calculation
I need to be able to bring in a new column that can calculate the % varience between the "AvG Response Time" between test runs.
What make this complex for me is that I need to be able to consider that where the new build is better it should show it in a Positive % and bring in another column stating which build is the "Winning build". So there needs to be some way to compare which build is the new and old one. We might also want to be able to compare more that just 2 builds against each other.
I am presenting data on the PBI report using the Matrix visual and I would like it to show something like this.
Below is my sample file
Test Run | FEB Final 5/10/2019 9:01 AM | FEB Final 5/9/2019 9:51 AM | ||
Transaction name | Current Average | Current Average | Diffirence of | % Change |
KCW01_01_HomePage | 4 | 4 | 0 | 0% |
KCW02_01_EngagementDashboard | 6 | 5 | 1 | 16.67% |
KCW03_01_EngagementScreen | 5 | 5 | 0 | 0% |
KCW04_01_AtlasDashboard | 8 | 8 | 0 | 0% |
KCW04_02_AuditPlanningHomePage | 9 | 9 | 0 | 0% |
KCW04_03_PreEngagement | 8 | 7 | 1 | 12.50% |
KCW04_04_Strategy | 22 | 20 | 2 | 9.09% |
KCW05_01_RiskAsssessmentHomePage | 10 | 10 | and so on | and so on |
KCW05_02_EntityanditsEnvironment | 11 | 11 | and so on | and so on |
KCW05_03_MinutesHomePage | 10 | 10 | and so on | and so on |
KCW05_04_PlanningAnalytics | 6 | 6 | and so on | and so on |
KCW05_05_Inquiries | 4 | 4 | and so on | and so on |
KCW05_06_BusinessProcess | 3 | 3 | and so on | and so on |
KCW05_07_RAPD | 3 | 3 | and so on | and so on |
KCW09_01_MaterialityHomePage | 9 | 8 | and so on | and so on |
KCW09_02_DetermineMateriality | 6 | 6 | and so on | and so on |
KCW09_03_DeterminePMHomePageLoad | 8 | 7 | and so on | and so on |
KCW10_01_PRP | 9 | 9 | and so on | and so on |
KCW12_01_FraudHome | 9 | 9 | and so on | and so on |
KCW12_02_FraudRiskAssessment | 7 | 7 | and so on | and so on |
KCW12_03_Response | 3 | 3 | and so on | and so on |
KCW13_01_01_CeramicNonIntegratedLoad | 8 | 6 | and so on | and so on |
KCW13_01_05_UnderstandingofITLoad | 6 | 5 | and so on | and so on |
KCW14_01_IndependenceLandingPage | 10 | 9 | and so on | and so on |
KCW14_02_IndependenceHomePage | 12 | 10 | and so on | and so on |
KCW14_03_ConfirmationsIESBA | 13 | 12 | and so on | and so on |
KCW14_04_ConfirmationsPrivate | 4 | 3 | and so on | and so on |
KCW16_02_ProcessMapping | 0 | 0 | and so on | and so on |
KCW16_03_Balance | This screen was not tested in this build so it should not calculate | 1 | Blank | Blank |
Solved! Go to Solution.
Hi @etwa702 ,
By my test based on your sample ,you could create the measure below.
% = VAR a = CALCULATE ( [Current Average], FILTER ( ALLSELECTED ( 'Sample'[Test Run] ), 'Sample'[Test Run] = "FEB Final 5/10/2019 9:01 AM" ) ) VAR b = CALCULATE ( [Current Average], FILTER ( ALLSELECTED ( 'Sample'[Test Run] ), 'Sample'[Test Run] = "FEB Final 5/9/2019 9:51 AM" ) ) RETURN DIVIDE ( a - b, a )
Here is the output.
Hope this can help you.
In addition, please note that do not post your actual data here.
Best Regards,
Cherry
Hi @etwa702 ,
By my test based on your sample ,you could create the measure below.
% = VAR a = CALCULATE ( [Current Average], FILTER ( ALLSELECTED ( 'Sample'[Test Run] ), 'Sample'[Test Run] = "FEB Final 5/10/2019 9:01 AM" ) ) VAR b = CALCULATE ( [Current Average], FILTER ( ALLSELECTED ( 'Sample'[Test Run] ), 'Sample'[Test Run] = "FEB Final 5/9/2019 9:51 AM" ) ) RETURN DIVIDE ( a - b, a )
Here is the output.
Hope this can help you.
In addition, please note that do not post your actual data here.
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.