topic Re: Calculating Changes on the fly between two tables in DAX Commands and Tips

Calculating Changes on the fly between two tables

zantarel — Wed, 27 Nov 2024 14:30:45 GMT

Hi all

Been struggling with this one and hoping someone can help ... so I've got a couple of Tables - let's call them Scores and Uplifts.

Scores:

Name	Score
A	2
B	2
C	1
D	3
E	2

Uplifts:

Item	Name	Score Uplift
Item 1	A	0.5
Item 2	A	1.0
Item 1	B	0.5
Item 3	E	0.5

What I want to do is calculate the the averages of the numbers, based on the 'Item' selected.
The current average is easy - just Average (NumberColumn) = (2+2+1+3+2)/5 = 2

But then I want a way of saying based on the Items selected, the new average would be: XX
i.e. Selecting Item 1 and 2, the new average would be: ((2+0.5+1)+(2+0.5)+1+3+2 )/6= 2.4

Because Items 1 and 2 both affect A, so both of their uplifts need to be added to A. Whereas B is only being uplifted by Item 1, and I've not selected Item 3, so that uplift of 0.5 hasn't been included.

If I do this as a calculated column, I can easily lookup the score value and add the uplift onto that, but then when I average that, the original score essentially gets added twice - i.e. I'd end up with the below, which gives a different average, and then when I select only Items 1 and 2, the average score will just be those scores, not all scores but just those uplifts.

Item	Name	Score Uplift	original Score	Uplifted score
Item 1	A	0.5	2	2.5
Item 2	A	1.0	2	3
Item 1	B	0.5	2	2.5
Item 3	E	0.5	2	2.5

I don't seem to be able to get this to work at all and my brain just can't work out the logic I need to do this in a measure... I hope this makes sense...

Thanks in advance

Re: Calculating Changes on the fly between two tables

johnt75 — Wed, 27 Nov 2024 14:30:46 GMT

If you have a separate table of the names, which links to both scores and uplifts, you can use that in the visual and write a measure like

Combined Average = DIVIDE ( SUM ( Scores[Score] ) + SUM ( Uplifts[Score Uplift] ), COUNTROWS ( Scores ) + COUNTROWS ( Uplifts ) )

Re: Calculating Changes on the fly between two tables

FreemanZ — Wed, 27 Nov 2024 14:51:54 GMT

hi zantarel ,

try like:

measure =

DIVIDE(

SUM(uplifts[score uplift]) + SUM(scores[score]),

DISTINCTCOUNT(scores[name])

)

or?

Re: Calculating Changes on the fly between two tables

zantarel — Wed, 27 Nov 2024 15:05:09 GMT

Ok, so I was overthinking this a little bit and that has helped. The problem is, when I filter it by the item, it removes all Names that aren't related to that Item.
So the overall averages work a treat if all items are selected. but the problem comes when I filter it. so I do have a table with just the names in it, which has links to both the scores and the uplifts tables. so if i do all the calculations within the names table, but the filter is based on the items in uplifts, it removes any item in the names table that doesn't have that item. and then averages are just the averages of those items, not all items with those uplifts.
for the example above, that means I'm getting: an average of 2 with no uplifts - great.

And if I do all scores + all uplifts / count of rows = 2.5 - great 😄

but then if i filter the visual to just show me items 1+2, that's giving me the average of (2.5+3.5)/2 = 3 whereas it should be 2.4 - it's ignoring the Names of stuff that doesn't have that mapping. and short of mapping every Item to every Name with a value of 0 which is kinda crazy and would be a very very big table, i'm not sure how to get to the average i'm looking for?

Re: Calculating Changes on the fly between two tables

johnt75 — Wed, 27 Nov 2024 16:45:48 GMT

It sounds like either there's a relationship between scores and uplifts, which there shouldn't be, or the relationship between the names table and uplift is bi-directional, which it shouldn't be. The relationship from names to both tables and uplift should be single direction so that names filters the other tables, but the other tables do not filter names.

Re: Calculating Changes on the fly between two tables

zantarel — Wed, 27 Nov 2024 17:06:01 GMT

oh! I forgot about the relationships part of it... I think I've got it then! Thank you so much!!