cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
etwa702 Frequent Visitor
Frequent Visitor

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

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 NameSEP FinalOCT Final% DiffirenceWinning Build
Page 13267%OCT Final
Page 24757%SEP Final
Page 36875%SEP Final
Page 4770%SAME Result
Page 58563%OCT Final
Page 6440%SAME Result
Page 7550%SAME Result
Page 83650%SEP Final
Page 94580%SEP Final
Page 103267%OCT Final
Page 116350%OCT Final
Page 127457%OCT Final
Page 138563%OCT Final

 

My data set example below:

 

BuildBuild IndexDate TimeTransaction NameAvg Response Time
SEP Final19/20/2019 17:00Page 13
SEP Final19/20/2019 17:00Page 24
SEP Final19/20/2019 17:00Page 36
SEP Final19/20/2019 17:00Page 47
SEP Final19/20/2019 17:00Page 58
SEP Final19/20/2019 17:00Page 64
SEP Final19/20/2019 17:00Page 75
SEP Final19/20/2019 17:00Page 83
SEP Final19/20/2019 17:00Page 94
SEP Final19/20/2019 17:00Page 103
SEP Final19/20/2019 17:00Page 116
SEP Final19/20/2019 17:00Page 127
SEP Final19/20/2019 17:00Page 138
SEP Final19/21/2019 14:00Page 13
SEP Final19/21/2019 14:00Page 24
SEP Final19/21/2019 14:00Page 36
SEP Final19/21/2019 14:00Page 47
SEP Final19/21/2019 14:00Page 58
SEP Final19/21/2019 14:00Page 64
SEP Final19/21/2019 14:00Page 75
SEP Final19/21/2019 14:00Page 83
SEP Final19/21/2019 14:00Page 94
SEP Final19/21/2019 14:00Page 103
SEP Final19/21/2019 14:00Page 116
SEP Final19/21/2019 14:00Page 127
SEP Final19/21/2019 14:00Page 138
OCT Final210/1/2019 13:00Page 12
OCT Final210/1/2019 13:00Page 27
OCT Final210/1/2019 13:00Page 38
OCT Final210/1/2019 13:00Page 47
OCT Final210/1/2019 13:00Page 55
OCT Final210/1/2019 13:00Page 64
OCT Final210/1/2019 13:00Page 75
OCT Final210/1/2019 13:00Page 86
OCT Final210/1/2019 13:00Page 95
OCT Final210/1/2019 13:00Page 102
OCT Final210/1/2019 13:00Page 113
OCT Final210/1/2019 13:00Page 124
OCT Final210/1/2019 13:00Page 135

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

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

9 REPLIES 9
Nathaniel_C Super Contributor
Super Contributor

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

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

 

PercDif.PNG

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 Super Contributor
Super Contributor

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

 

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

 

PercDif3.PNG

 

PercDif2.PNG

 

 

PercDif1.PNGl

 

 

etwa702 Frequent Visitor
Frequent Visitor

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

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.

Highlighted
Nathaniel_C Super Contributor
Super Contributor

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

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 Frequent Visitor
Frequent Visitor

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

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!!!

Nathaniel_C Super Contributor
Super Contributor

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

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")))

 

 

PercDif4.PNG

Nathaniel_C Super Contributor
Super Contributor

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

Tested on page 2

etwa702 Frequent Visitor
Frequent Visitor

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

@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 Super Contributor
Super Contributor

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,730)