Forum Discussion
Summing values using a Hash value
Hi everyone,
Ritaf1983 thank you - you have previously helped me out with something similar, but I'm still a bit confused and this is slightly different!
I have 3 tables which basically look like this (simplifed - in real life tables are much bigger)
I need to work out how many times all the Colours appear given the groups and their hash codes listed in the first table.
For instance, A has the Hash Code 1111, which equates to the colours Red, Blue, Yellow, Green and Orange - so one count for each of these colors.
B has the Hash Code 2222 = Red, Blue, Pink, Purple and Grey. This means Red has appeared 2 times in total, Blue 2 times, Yellow, Green, Orange , Purple and Grey once.
Continue for all the other groups, decipher their hash to get the total count of colours.
How do I do this? How do I get the total count of each colour? If I try to sum the HashCode/Colour table somehow, how do I refer to the table with the Groups in it?
Just for fun I then also have to sum up all the scores per colour.. although I reckon I could work that out if I could work out my first question....
Thanks in advance everyone!!!
Hi naninamu ,
Thanks 3CloudThomas for the quick reply. I have some other thoughts to add:
(1) My test data is the same as yours.
(2) We can create a measure.
Measure 2 = COUNTROWS(FILTER('Table 1',[HashCode] in VALUES('Table 2'[HashCode])))(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi naninamu ,
Thanks 3CloudThomas for the quick reply. I have some other thoughts to add:
(1) My test data is the same as yours.
(2) We can create a measure.
Measure 2 = COUNTROWS(FILTER('Table 1',[HashCode] in VALUES('Table 2'[HashCode])))(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! That sounds quite simple ... maybe so was overthinking it... 😁
Hi Anonymous - probably me but when I do that all I'm getting is the HashCode/Colour table counting the colours in that table.
What I need is illustrated below. So if I put in the Group/HashCode/Colour into one table I get this:
So my final output needs to be a count of each colour ie
Blue = 10
Green = 5
Grey = 7 etc
Any help appreciated!!
Thanks so much. Could you sort of explain as if to an 8 year old exactly what is happening in the 2 Measures you've written? I'm still getting my head around exactly what they all mean...
Hi,
Based on the 3 tables shared, show the expected result very clearly. Also, share data in a format that can be pasted in an MS Excel file.
