cancel
Showing results for
Did you mean:
Member

## How to create a new percentage measure for specific rows only

Hi PowerBi Community! I'm new to this software but we are using it a lot lately in my company as its potentialities are amazing and we will try to modernize our reports in order to make the most of PowerBi efficiency.

For this I'm trying to reshape a power pivot report into a much efficient and good loking power Bi one.

my dataset as a column that identifies the number of passengers for a single sector (Origin & Destination e.g. LHR to DXB). Another Column identifies if this passengers flows is from this year or last year.

What I need to do:

The new power Bi dashoboard needs to have an indicator that tells the user the variation of passengers for each single Origin&Destination Sector (they are more than 6000). This variation has to be in percentage and absolute figure.

What we have in power pivot is this:

where Mkt vLY is the percentage variation of LHRDXB 2017 vs 2016 and -0.0004 is the same but in absolute values.

What I have tried to do:

I've tried to split 2016 and 2017 data in two different tables and calculate a column that substract then divide but it did not work out.

Thank you guys

Alberto

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: How to create a new percentage measure for specific rows only

Change the Max to

FILTER(ALL(Table1[Year]),Table1[Year]=MAX(Table1[Year]))

MFelix

Proud to be a Datanaut!

Highlighted
Member

## Re: How to create a new percentage measure for specific rows only

Hi @MFelix,

it worked perfectly. I had to change the MAX condition into MIN in the prev year part in order to pick up the correct value.

Thanks a lot!!

9 REPLIES 9
Super User

## Re: How to create a new percentage measure for specific rows only

Hi @abartozzi,

You can calcule this by using the TOTALYTD values and adding the previous year, something that looks like this:

```Mkt vLY=
var Mkt_Current_Year = TOTALYTD (
SUM(Marketing[Mkt Size]),
Calendar[Date])
var Mkt_Previous_Year = TOTALYTD (
SUM(Marketing[Mkt Size]),
Calendar[Date],-1,Year)
Return
Divide(Mkt_Current_Year,Mkt_Previous_Year)-1

```

Didn't test this out but the formula should be close to this.

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: How to create a new percentage measure for specific rows only

Hi,

Try this.Hope this helps

Create two measures as below

1. CurrentSpend=SUM(<ColumnName>)

2. PreviousSpend = CALCULATE(SUM(<ColumnName>),SAMEPERIODLASTYEAR(<DateColumn>))

Now create one more measure as below

Variation=FORMAT(IF(CurrentSpend>PreviousSpend,(CurrentSpend-PreviousSpend)/PreviousSpend,(PreviousSpend-CurrentSpend)/PreviousSpend),"Percent")

Member

## Re: How to create a new percentage measure for specific rows only

Hi MFelix,

thank you for your prompt reply. Seems like a good solution however I have a problem with the table you called calendar and column identified as Date.

in my case this is called 'Year' and it includes values for this year and last year where 2016 and 2017 are text strings therefore what PowerBi does not returns anything.

Sorry I forgot to mention before

Member

## Re: How to create a new percentage measure for specific rows only

it looks as another great solutions, but the problem I was mentioning to MFelix is that the column that identifies the year is a text string (eventually could be an integer number).

:/

Super User

## Re: How to create a new percentage measure for specific rows only

I don't know if you are putting the value of year in a slicer but if you do you can change the formula to this:

```Mkt vLY=
var Mkt_Current_Year = CALCULATE (
SUM(Marketing[Mkt Size]),
Max(Marketing[Year]))
var Mkt_Previous_Year = CALCULATE (
SUM(Marketing[Mkt Size]),
Max(Marketing[Year]-1)
Return
Divide(Mkt_Current_Year,Mkt_Previous_Year)-1```

Regards,

MFelix

Proud to be a Datanaut!

Member

## Re: How to create a new percentage measure for specific rows only

this way looks better but now the issue is:

Super User

## Re: How to create a new percentage measure for specific rows only

Change the Max to

FILTER(ALL(Table1[Year]),Table1[Year]=MAX(Table1[Year]))

MFelix

Proud to be a Datanaut!

Super Contributor

## Re: How to create a new percentage measure for specific rows only

Hi @abartozzi,

Why the year is text? Is it text data type in Excel? Please click Query Edit, and see if the data type are changed. Change it to number data type, you will get expected result using the solution above.

If you still have problem, could you please share your sample data or more details for further analysis.

Best Regards,
Angelia

Highlighted
Member

## Re: How to create a new percentage measure for specific rows only

Hi @MFelix,

it worked perfectly. I had to change the MAX condition into MIN in the prev year part in order to pick up the correct value.

Thanks a lot!!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 34 members 898 guests
Recent signins: