Forum Discussion
Add column based off another column category - formaula help needed
Hi guys,
I have the following sports table that im hoping to add a couple of columns to. i have the information in the black, but i would like to add the information in the red
Club | Opposition | Points scored | Points conceded | Points difference
Team A Team B 20 30 -10
Team B Team A 30 20 10
Any help would be much appreciated!
Thanks!
Hi js1289,
If you want to compare value between each Club, i think you could fun join table like below
let Source = Table.NestedJoin(T4, {"Round"}, T4, {"Round"}, "T4", JoinKind.FullOuter), #"Expanded T4" = Table.ExpandTableColumn(Source, "T4", {"Round", "Club", "Points Scored"}, {"Round.1", "Club.1", "Points Scored.1"}) in #"Expanded T4"RoundClubPoints Scored
1 A 20 1 B 30 1 C 10 1 D 15 2 A 35 2 B 30 2 C 25 2 D 20 Then you will get result like below
You could remove column and modify your table to see whether it work or not.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hi Anonymous
try 2 columns
Points conceded = lookupvalue(Table1[Points scored];Table1[Club];[Opposition]) Points Difference = [Points scored]-[Points conceded]It should be enough, but I've got a lot of experience in the sports stats analysis and i could advice you to use matchday_id or match_id index column for long-term competitions
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thanks for your help guys az38 and Anonymous ! I tried this and it worked perfectly although I do now realise I will have multiple games for different rounds and teams....so the table will end up
Round Club Opposition Points Scored Points Conceded Points difference 1 A B 20
30 -10 1 B A 30 20 10 1 C D 10 15 -5 1 D C 15 10 5 2
A C 35 25 10 2 B D 30 20 10 2 C A 25 35 -10 2 D B 20 30 -10 Still trying to populate the two columns in red so Im guessing its a similar formula with extra factors taking such as club and round?
Thanks again!
Hi js1289,
If you want to compare value between each Club, i think you could fun join table like below
let Source = Table.NestedJoin(T4, {"Round"}, T4, {"Round"}, "T4", JoinKind.FullOuter), #"Expanded T4" = Table.ExpandTableColumn(Source, "T4", {"Round", "Club", "Points Scored"}, {"Round.1", "Club.1", "Points Scored.1"}) in #"Expanded T4"RoundClubPoints Scored
1 A 20 1 B 30 1 C 10 1 D 15 2 A 35 2 B 30 2 C 25 2 D 20 Then you will get result like below
You could remove column and modify your table to see whether it work or not.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
After I saw az38 's answer, the LOOKUPVALUE is a better way to solve it, the measure can be:
Meausre = LOOKUPVALUE('Table'[POINT],'Table'[OPPOSITION],MAX('Table'[CLUB]),'Table'[ID],MAX('Table'[ID]),"NO VALUE")Aiolos Zhao
Anonymous
i think calculated column will work a little bit faster then and will need in less memory consumption. but Im not sure 🙂
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi Anonymous ,
I think you must have an ID or a key to distinguish every game.
So I made up below sample data:
ID CLUB OPPOSITION POINT 1 A B 20 1 B A 30 2 A B 10 2 B A 50 My measure :
Measure = CALCULATE(SUM('Table'[POINT]),ALLEXCEPT('Table','Table'[ID])) - SUM('Table'[POINT])Measure 2 = SUM('Table'[POINT]) - (CALCULATE(SUM('Table'[POINT]),ALLEXCEPT('Table','Table'[ID])) - SUM('Table'[POINT]))And result:
Aiolos Zhao
