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!

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

:/

Highlighted
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

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

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 47 members 958 guests
Recent signins: