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.
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 |
Solved! Go to Solution.
See above
Proud to be a Super User!
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"))
Proud to be a Super User!
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
Proud to be a Super User!
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.
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.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
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!!!
Here is my actual PBI file, so we could perhaps work together.
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?
See above
Proud to be a Super User!
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")))
Proud to be a Super User!
Tested on page 2
Proud to be a Super User!
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.