Compare all other cells in one column against one selectcell from same column (same column division)

[dengydongn]

Hello all,

I have a trivial question regarding same column division, say I'm running A/B testing, with below observations,

What I need is to show how much improvements each of my change has against the base (control), 

flight id	performance
flight1	0.5%
flight2	0.4%
control	0.3%
flight3	0.6%

Keep in mind here flight1, flight2, or control would be any string, there could be more flight4, flight5, etc. order in the table is unfixed, so I cannot access "control" by index but only by name. So I need to access [flight id]{"control"} this cell specifically as the base unit then do all the calcuation, not sure how to even start...

Eventually I'll need this, here 66% is from 0.5% / 0.3% and so on

flight1	66%
flight2	33%
flight3	100%

So my question is, how do I do this in Power BI desktop, I wouldn't mind building a 2nd table first then draw a simple chart based on it, or write DAX for measurement, whichever is easier.

Thanks!!!

[parry2k · Accepted solution]

@dengydongn add these measure and should be good to go:

Base Performance = SUM ( Flight[performance] )

Control Performance = CALCULATE ( [Base Performance], Flight[flight id] = "Control" )

Flight Performance against control = DIVIDE ( [Base Performance], [Control Performance] ) - 1

use last measure in your visual and you can filter the visual to not to show the control

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@dengydongn yes that's exactly what it is doing. 

The solution @CNENFRNL  provided is great but in a larger schema of things I will avoid creating a calculated table and the big reason is if you are slicing and dicing the data, these percentages will not be dynamic but it all depends on the use case. cheers!!

[CNENFRNL · Accepted solution]

Derived table,

COMPARISON = 
VAR __c =
    SUMX( FILTER( FLIGHT, FLIGHT[flight id] = "control" ), FLIGHT[performance] )
RETURN
    SELECTCOLUMNS(
        FILTER( FLIGHT, NOT FLIGHT[flight id] = "control" ),
        "Fligt ID", FLIGHT[flight id],
        "Comparison", DIVIDE( FLIGHT[performance], __c ) - 1
    )

[parry2k · Accepted solution]

@dengydongn add these measure and should be good to go:

Base Performance = SUM ( Flight[performance] )

Control Performance = CALCULATE ( [Base Performance], Flight[flight id] = "Control" )

Flight Performance against control = DIVIDE ( [Base Performance], [Control Performance] ) - 1

use last measure in your visual and you can filter the visual to not to show the control

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dengydongn]

Thanks, this solved my problem like a charm, if I understand this correctly, first measure basically copies the perf column, 2nd mesaure copies the control perf and apply to all columns, 3rd measure does a division on base / control, is that correct? the function SUM was a bit confusing as I thought it would sum all values in the same column but turned out it merely copied the column