I have a pivot table of my data and that has data broken out among rows and columns. I have a value called CPU% that is broken down by test_case(rows) and software_version(columns). I can see the difference in the CPU% from one software_version to the next with a table across calculation; but I want to be able to select a specific software_version via a parameter called reference_version and I’d like all the difference columns to be calculated based on a difference between the particular field and the reference_version field.
I know this has to be possible and I tried playing around with a few examples from the Q&A, but none seemed to quite solve my problem. Maybe someone on here knows the combination of calculated fields I need to achieve this or has solved something like this before.
Any help would be appreciate and thanks in advance.
You can achieve the desired behavior by adding the following calculated fields to your analysis and pivot table (note that I only split the calculation into two fields for sake of easier readability). In my example, the parameter is called pReferenceVersion.
Add a calculated field called reference_cpu%, which calculates the average reference CPU% value across all rows per test case:
On top of that you can add a second calculated field called difference_to_reference_cpu%, which simply calculates the difference between the CPU% value of each specific version vs its reference CPU% value:
{CPU%}-{reference_cpu%}
Once added to the pivot table and selecting Average as aggregate option of all values, the result looks as follows:
The only way I can get a calculation like yours is if I add my host_version to the list of partition fields, but then everything is 0 except the reference version. When I change reference_avg_value to this:
Hi @Zach6,
You are right, my first example dataset was a very simplified dataset. I’ve just update the original post to reflect the average calculation that needs to be done for the CPU% calculation when you have multiple rows per test_case and software_version.
I hope this works for your dataset as well. If so, please mark it as a “Solution!”. Thanks!
