Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Change the Max to
FILTER(ALL(Table1[Year]),Table1[Year]=MAX(Table1[Year]))
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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!!
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
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")
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).
😕
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsthis way looks better but now the issue is:
probably relates to your observation...
Change the Max to
FILTER(ALL(Table1[Year]),Table1[Year]=MAX(Table1[Year]))
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |