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 Team

## 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 Team

## 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.
Highlighted
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 Team

## 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.
Community Support Team

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

#### 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.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors