Summing values using a Hash value

[naninamu]

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!!!

[Ashish_Mathur]

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.

[Anonymous]

Change the table to a Matrix. This will produce Drill Down hierarchy for the attributes

[naninamu]

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... 

[Anonymous · Accepted solution]

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. 

[naninamu]

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!!

[naninamu]

Thank you! That sounds quite simple ... maybe so was overthinking it... 😁

[Anonymous]

Create a Simple Count measure on the table with HashCode and Color. Then, just display the Color column in a table (or matrix, or column chart) and the measure. Do not include the HashCode column in the visuals