Forum Discussion
Help, how d I get % change?
Hi
I am new to power BI and have two issues:
I have to different data sets, one for 2020 and one for 2021. How can I show the percent change for each row?
Additionally in my data query, I have states in a column, I need to move these in order to break out the states by region not just all states? Problem is when I move the data it changes all my other data fields? I attached photos. I want to do membership the same way but be able to put different states by region in my visuals. Thanks so much!
17 Replies
Hi bv27718n,
Couple Suggestions:
1. Create a Region table that contains the State and Region it belongs to, then join that table to your facts, use the Region table to filter the fact data
2. Either Union your 2020 and 2021 data together and add a period column, more extensible as you add more periods || or created calc columns using lookups in the 2021 table to get the 2020 data you want to compare for each row, not as extensible for additional years.
If you are able to provide sample data as opposed to screen shots that would be very helpful.
Thanks,
Hi,
I am still confused. not sure what to do. how can I share data here? Thanks!!
Hi bv27718n,
you can copy and paste the data into a table in the thread, or you can share a pbix through onedrive or google drive
StatePF LifePF PatronPF Gold Patron 21PF Elite Mbrs 2021QF LifeQF PatronQF Gold PatronQF Elite MbrsPF/QF Gold Patron 21PF/QF Patron 21PF/QF Life 21
AK 20 0 0 20 0 0 0 0 0 0 20 AL 14 4 0 18 19 5 0 24 0 9 33 AR 14 0 1 15 18 0 0 18 1 0 32 AZ 79 3 1 83 19 0 0 19 1 3 98 CA 108 3 0 111 13 0 0 13 0 3 121 CO 263 7 3 273 18 0 1 19 4 7 281 CT 17 0 0 17 1 0 0 1 0 0 18 DE 8 0 0 8 2 0 0 2 0 0 10 FL 75 2 0 77 28 6 1 35 1 8 103 GA 42 1 0 43 60 6 0 66 0 7 102 HI 4 0 0 4 0 0 0 0 0 0 4 IA 645 20 4 669 39 0 0 39 4 20 684 ID 46 1 0 47 3 0 0 3 0 1 49 IL 338 7 1 346 40 0 0 40 1 7 378 IN 156 0 0 156 16 1 0 17 0 1 172 KS 237 4 1 242 45 5 3 53 4 9 282 KY 17 0 0 17 14 0 0 14 0 0 31 LA 10 0 0 10 2 0 0 2 0 0 12 MA 15 0 0 15 0 0 0 0 0 0 15 MD 31 0 0 31 5 0 0 5 0 0 36 ME 7 0 0 7 1 0 0 1 0 0 8 MI 310 11 1 322 8 1 1 10 2 12 318 MN 1378 55 9 1442 42 2 1 45 10 57 1420 MO 86 2 1 89 76 3 0 79 1 5 162 MS 16 0 0 16 8 0 0 8 0 0 24 MT 166 1 1 168 4 0 0 4 1 1 170 NC 34 1 0 35 18 0 0 18 0 1 52 ND 220 1 0 221 0 0 0 0 0 1 220 NE 345 24 3 372 21 5 0 26 3 29 366 NH 8 0 0 8 0 1 0 1 0 1 8 NJ 12 0 0 12 1 0 0 1 0 0 13 NM 13 0 0 13 3 0 0 3 0 0 16 NV 29 0 0 29 3 0 0 3 0 0 32 NY 40 1 0 41 4 1 0 5 0 2 44 OH 208 8 0 216 14 3 0 17 0 11 222 OK 21 0 0 21 52 2 0 54 0 2 73 OR 47 0 0 47 5 0 0 5 0 0 52 PA 118 0 0 118 5 0 0 5 0 0 123 RI 2 0 0 2 0 0 0 0 0 0 2 SC 16 2 1 19 14 3 0 17 1 5 30 SD 499 14 14 527 10 1 1 12 15 15 509 TN 39 4 0 43 30 2 0 32 0 6 69 TX 122 9 1 132 115 13 3 131 4 22 237 UT 30 0 0 30 1 0 0 1 0 0 31 VA 39 0 1 40 11 1 0 12 1 1 50 VT 3 0 2 5 0 1 1 2 3 1 3 WA 111 2 0 113 3 0 0 3 0 2 114 WI 364 9 3 376 11 2 0 13 3 11 375 WV 7 0 0 7 3 0 0 3 0 0 10 WY 73 1 0 74 0 0 0 0 0 1 73 thanks! does that help?
Hi bv27718n ,
Please Share your pbix file, or an excel copy of the data, the data you have pasted does not import properly into Power BI
Are you able to create custom look up, to do the calc between two different data sets? I have one for membership 5.20 and 5.21, would you just link in order to see percent change for each?
https://drive.google.com/file/d/1xWl-pLZ3m6xinlepSwWI1j7mRxaTJjxl/view?usp=sharing
Thanks, I want to get the change from the 5.20 elite to 5.21 elite in the PF elite and QF elite sheets and then figure out how i can seperate the states so I can add visuals of by region
Hi bv27718n,
Looking at your file, you do not need the relationship between the Elite 20 and 21 tables.
Below is a formula that will create a new column in your table with the other tables value
PF Life 2020 col = VAR _state = 'Elite 5.21'[State] return CALCULATE(MAX('Elite 5.20'[PF life]), FILTER('Elite 5.20', 'Elite 5.20'[State] = _state))you can then create the following measures to get the sums and variance
PF Life 2020 = SUM('Elite 5.21'[PF Life 2020 col]) pf life 2020 var = sum('Elite 5.21'[PF Life]) - sum('Elite 5.21'[PF Life 2020 col])Result would look like this
I have attached your file with my mods
To slice by Region, you will need to create a region table that contains each state and region then relate the table to the fact tables on the state. You will then use the the region to filter and slice your data.
Please review the following Microsoft docs for best practices for data modelling
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
I would also suggest that you store your facts in a single fact table with a period column to define the period that the fact belongs to rather than creating tables for each year, it is not a manageble or extensible solution.
Thanks,
Okay, makes sense. I appreciate your help. I did have one last follow up, I am looking at what you did, how do you then add in the other colums likethe PF 2020? I would go into the elite 2020, create columns say for gold patron and patron and then go from there I take it.
Is there an easy way to create a "state region" table? where is that created?
Awesome, thanks! So, I just continue to do that and I can add in a percent change column as well. How come you do not need the relationship there? I thought you did.
Hi bv27718n,
The Facts really are not related, you could have related on State 1 to 1 but that was not the case
I dont think I can create a custom colum to pull in the calcs but the data sets I am using are on seperate pagesthough. right?
Hi bv27718n ,
Sorry, I don’t understand your needs. Can you describe it more accurately? It’s best to provide a screenshot of the desired result. I will answer you as soon as possible.
Best Regards,
Henry
