Comapring Number Values from same column with text values from same column

[etwa702]

Hi all

@BIHelp 

@Greg_Deckler 

I need some help please.

Some background for context.

- 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 need to be able to bring in a new column that can calculate the % diffirence between the "AvG Response Time" Column of "SEP Final" with the "AvG Response Time" Column of "OCT Final"

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.

Transaction Name	SEP Final	OCT Final	% Diffirence	Winning Build
Page 1	3	2	67%	OCT Final
Page 2	4	7	57%	SEP Final
Page 3	6	8	75%	SEP Final
Page 4	7	7	0%	SAME Result
Page 5	8	5	63%	OCT Final
Page 6	4	4	0%	SAME Result
Page 7	5	5	0%	SAME Result
Page 8	3	6	50%	SEP Final
Page 9	4	5	80%	SEP Final
Page 10	3	2	67%	OCT Final
Page 11	6	3	50%	OCT Final
Page 12	7	4	57%	OCT Final
Page 13	8	5	63%	OCT Final

My data set example below:

Build	Build Index	Date Time	Transaction Name	Avg Response Time
SEP Final	1	9/20/2019 17:00	Page 1	3
SEP Final	1	9/20/2019 17:00	Page 2	4
SEP Final	1	9/20/2019 17:00	Page 3	6
SEP Final	1	9/20/2019 17:00	Page 4	7
SEP Final	1	9/20/2019 17:00	Page 5	8
SEP Final	1	9/20/2019 17:00	Page 6	4
SEP Final	1	9/20/2019 17:00	Page 7	5
SEP Final	1	9/20/2019 17:00	Page 8	3
SEP Final	1	9/20/2019 17:00	Page 9	4
SEP Final	1	9/20/2019 17:00	Page 10	3
SEP Final	1	9/20/2019 17:00	Page 11	6
SEP Final	1	9/20/2019 17:00	Page 12	7
SEP Final	1	9/20/2019 17:00	Page 13	8
SEP Final	1	9/21/2019 14:00	Page 1	3
SEP Final	1	9/21/2019 14:00	Page 2	4
SEP Final	1	9/21/2019 14:00	Page 3	6
SEP Final	1	9/21/2019 14:00	Page 4	7
SEP Final	1	9/21/2019 14:00	Page 5	8
SEP Final	1	9/21/2019 14:00	Page 6	4
SEP Final	1	9/21/2019 14:00	Page 7	5
SEP Final	1	9/21/2019 14:00	Page 8	3
SEP Final	1	9/21/2019 14:00	Page 9	4
SEP Final	1	9/21/2019 14:00	Page 10	3
SEP Final	1	9/21/2019 14:00	Page 11	6
SEP Final	1	9/21/2019 14:00	Page 12	7
SEP Final	1	9/21/2019 14:00	Page 13	8
OCT Final	2	10/1/2019 13:00	Page 1	2
OCT Final	2	10/1/2019 13:00	Page 2	7
OCT Final	2	10/1/2019 13:00	Page 3	8
OCT Final	2	10/1/2019 13:00	Page 4	7
OCT Final	2	10/1/2019 13:00	Page 5	5
OCT Final	2	10/1/2019 13:00	Page 6	4
OCT Final	2	10/1/2019 13:00	Page 7	5
OCT Final	2	10/1/2019 13:00	Page 8	6
OCT Final	2	10/1/2019 13:00	Page 9	5
OCT Final	2	10/1/2019 13:00	Page 10	2
OCT Final	2	10/1/2019 13:00	Page 11	3
OCT Final	2	10/1/2019 13:00	Page 12	4
OCT Final	2	10/1/2019 13:00	Page 13	5

[Nathaniel_C]

Hi @etwa702 ,

See pic below for solution except the sort on Transaction Name. 4 measures following.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Sep Final = Calculate(MAX(percentDif[Avg Response Time]),percentDif[Build Index]=1)

Oct Final = Calculate(MAX(percentDif[Avg Response Time]),percentDif[Build Index]=2)

% Difference = If ([Oct Final]<[Sep Final],Divide([Oct Final],[Sep Final]), IF([Sep Final]<[Oct Final],DIVIDE([Sep Final],[Oct Final]),0))

Winning Build = IF([Oct Final]>[Sep Final],"Oct Final", If([Sep Final]>[Oct Final],"Sept Final","SAME Result"))

[Nathaniel_C]

Hi @etwa702 ,

Here is a work around due to the matrix visual not being able to sort by column. Created column with the formula showing in the pic. Then shrink the visual, so that column is not showing.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

l

[etwa702]

Hi @Nathaniel_C , Thanks for the help!

I might be missing a variable or a extra step here. Could you send me your sample file if you have not trashed it already?

I get the following when trying to add the measure for "SEP Final"

Failed to resolve name 'percentDif'. It is not a valid table, variable, or function name.

[Nathaniel_C]

Hi @etwa702 ,

PercentDif is the table name.  Sometimes the errors appear due to missing syntax. Anyway here is the file. It is a recovered file, and not sure where the hierachies came from, but didn't use them. Let me know if you have any questions.

PBIX File

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[etwa702]

WOW, this looks great!

Just saw this on my dataset and I love it.

i did notice that based on my data there are some Test Runs that did not have a result for the Transaction name, must be because that test did not test that page.

Example, OCT build tested 10 pages only and SEP build tested all 13 pages, SEP build would have a value and based on that then states that it is the winning build and it should instead not show any calculation.

Possible to help me with filtering out the blank values?

Thank you for this, great help!!!

[etwa702]

@Nathaniel_C 

Here is my actual PBI file, so we could perhaps work together.

My PBI Maser

I added the measures in (noticed the % diffirence measure breaks, still figuring out why).

Added the new Winning Build, (which now workes with blanks, THANKS).

I have test results passing into new months, do I need to build the DAX for each of them or is there some way I can do the calculations dynamically so they span accross new data that comes into the file?

[Nathaniel_C · Accepted solution]

See above

[Nathaniel_C]

Hi @etwa702 ,

OK, created a new measure "...Revised" That wrapped "Winning Build with an if statement. So just compare the two and make the change.  You could if you wish define the granularity even more, and add another if to determine whether it is "Sept Test not performed."

Glad I could help!

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Winning Build Revised = IF( ISBLANK([Sep Final]) || ISBLANK([Oct Final]),"Test not Performed", IF([Oct Final]>[Sep Final], "Oct Final" , If([Sep Final]>[Oct Final],"Sept Final","SAME Result")))

[Nathaniel_C]

Tested on page 2