Forum Discussion
Comapring Number Values from same column with text values from same column
Hi all
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 |
See above
9 Replies
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.
NathanielSep 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"))
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.
Nathaniell
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.
