Help, how d I get % change?

[bv27718n]

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!

[v-henryk-mstf]

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

[bv27718n]

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?

[bv27718n]

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

[bv27718n]

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. 

[richbenmintz]

Hi @bv27718n,

The Facts really are not related, you could have related on State 1 to 1 but that was not the case

[richbenmintz]

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,

[bv27718n]

awesome and for that table, is there anything that should be linked or any other steps to pull that info in?

[richbenmintz]

Hi @bv27718n ,

Import the file into your Power BI Data Model and relate the table to your facts on the state column

[bv27718n]

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? 

[richbenmintz]

Hi @bv27718n ,

Follow the exact same pattern, create column in the table where the variance is by looking up the value from the corresponding table, then add your measures.

For the State Region Table you could use this csv and import into your model

https://github.com/cphalpert/census-regions/blob/master/us%20census%20bureau%20regions%20and%20divis... 

[bv27718n]

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?

[bv27718n]

thanks! does that help?

[richbenmintz]

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

[bv27718n]

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

[richbenmintz]

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, 

[bv27718n]

Hi,

I am still confused. not sure what to do. how can I share data here? Thanks!!

[richbenmintz]

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