cancel
Showing results for
Did you mean:
Frequent Visitor

## Percentage Difference for a single row

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Percentage Difference for a single row

Hi @Jd018,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
6 REPLIES 6
Super User I

## Re: Percentage Difference for a single row

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

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Community Support

## Re: Percentage Difference for a single row

Hi @Jd018,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

## Re: Percentage Difference for a single row

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] )`

Community Support

## Re: Percentage Difference for a single row

Hi @Jd018,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Community Support

## Re: Percentage Difference for a single row

Hi @Jd018,

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

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

## Re: Percentage Difference for a single row

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

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.