Percentage Difference for a single row

[Anonymous]

Hi there,

I have a table that contains 3 rows.

Is there a way I can show rows 2 & 3 as percentage differences from row 1?

Thanks,

JAson

[Anonymous]

Hi there,

I want to show % differences between my control and tests.

Imagine a table like this:

Control      25

Test A        30

Test B        18

I want to know as another column what Test A and then Test B are as a percentage of Control. How can I do this?

Thanks

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Create the measures as below.

Measure = CALCULATE(SUM(Table1[t1]))

rank = RANKX(ALL(Table1),[Measure])

perc = var onerow = CALCULATE(SUM(Table1[t1]),FILTER(Table1,[rank]=3))
var restrow = CALCULATE(SUM(Table1[t1]),FILTER(Table1,[rank]<>3))
return 
restrow/onerow

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[v-frfei-msft]

Hi @Anonymous,

I made one sample for your reference.

1. Insert an index column in Power query.

2, Create a measure as below.

perc = 
VAR row1 =
    CALCULATE ( SUM ( Table1[t1] ), FILTER ( Table1, Table1[Index] = 1 ) )
        + CALCULATE ( SUM ( Table1[t2] ), FILTER ( Table1, Table1[Index] = 1 ) )
        + CALCULATE ( SUM ( Table1[t3] ), FILTER ( Table1, Table1[Index] = 1 ) )
VAR restrow =
    CALCULATE ( SUM ( Table1[t1] ), FILTER ( Table1, Table1[Index] <> 1 ) )
        + CALCULATE ( SUM ( Table1[t2] ), FILTER ( Table1, Table1[Index] <> 1 ) )
        + CALCULATE ( SUM ( Table1[t3] ), FILTER ( Table1, Table1[Index] <> 1 ) )
RETURN
    row1 / restrow

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

This is really useful and thanks so much.

The only issue I have here is that the values I'm trying to calculate differences from are already measures.

So as an example  (Table1[t1]) would be a calculated measure already.

Thanks,

JAson

CALCULATE ( SUM ( Table1[t1] )

[PattemManohar]

@Anonymous Sample data and expected output will be really helpful to understand it better and also to solve the issue quicker.