Add column based off another column category - formaula help needed

[Anonymous]

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!

[Anonymous]

Thank you very much everyone!!

[AiolosZhao]

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 

[az38]

@AiolosZhao 

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

[AiolosZhao]

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

[az38]

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

LinkedIn

[Anonymous]

Thanks for your help guys @az38 and @AiolosZhao ! 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!

[dax · Accepted solution]

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AiolosZhao]

Hi @Anonymous ,

I think you can use my measure above your reply, I think that's what you want, if you want to create two calculated columns, I think the expression should be:

Points Conceded = LOOKUPVALUE('Table'[Points  Sored],'Table'[Opposition],MAX('Table'[Club]),'Table'[Round],MAX('Table'[Round]),"NO VALUE")

Points difference = [Points Scored] - [Points Conceded]

  Please try.

Aiolos Zhao

[az38]

@Anonymous 

highly reccomend to create a column match_id (unique for each couple of rows) - you could easily find a red columns by this key

do not hesitate to give a kudo to useful posts and mark solutions as solution