cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abartozzi Member
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. 

 

About the source Data:

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:Capture.PNG

 

 

 

 

 

 

 

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. 

 

Coul I you please help me with this?

 

Thank you guys

Alberto

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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



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

Proud to be a Datanaut!




View solution in original post

Highlighted
abartozzi Member
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!!Smiley Very Happy

 

 

View solution in original post

9 REPLIES 9
Super User
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]),
                                      DATEADD(
                                      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



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

Proud to be a Datanaut!




Ramadevi Frequent Visitor
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")

abartozzi Member
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

abartozzi Member
Member

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

Hi Ramadevi, 

 

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



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

Proud to be a Datanaut!




abartozzi Member
Member

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

this way looks better but now the issue is:

 

Capture.PNG

 

probably relates to your observation...

Super User
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



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

Proud to be a Datanaut!




View solution in original post

v-huizhn-msft Super Contributor
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
abartozzi Member
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!!Smiley Very Happy

 

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 34 members 898 guests
Please welcome our newest community members: