Calculate % increase or decrease by filtired values

[etwa702]

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

Sample data

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

[v-piga-msft · Accepted 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