How to create a new percentage measure for specific rows only

[Anonymous]

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:

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

[MFelix · Accepted solution]

Change the Max to 

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

MFelix

[v-huizhn-msft]

Hi @Anonymous,

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

[Ramadevi]

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

[Anonymous]

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

😕

[MFelix]

Hi @Anonymous,

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

[Anonymous]

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

[MFelix]

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

[Anonymous]

this way looks better but now the issue is:

probably relates to your observation...

[MFelix · Accepted solution]

Change the Max to 

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

MFelix

[Anonymous · Accepted solution]

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