SUM Column from one table based on value from another table

[patri0t82]

Hello,

I have two tables, Incidents Total and CR_Data. I need to perform this without any relationship between the tables.

In the CR_Data table I have a column called Tier 1, which contains names.

In the Incidents Total table, I have two columns, Name and Count.

I am trying to get a sum of 'Incidents Total'[Count] to appear beside each corresponding name in CR_Data[Tier 1]

This is the measure I have created:

Incidents Count =

CALCULATE(

SUM('Incidents Total'[Count]),

FILTER(

    'Incidents Total',

    'Incidents Total'[Name] = CALCULATE(VALUES('CR_Data'[Tier 1]))

    )

)

When I use this in a measure, it does not provide an error, it simply hides all the records and displays no data at all. I have tried it in a custom column in the CR_Data table, and the column fills with no values either.

Help is greatly appreciated!

[Anonymous]

Rather than a measure, create it as a column. The code you've created would work as a calculated column in your CR_Data table and it should retun the results you need. 

[patri0t82]

Hi Karlos, I appreciate your help, first of all. Thanks for taking the time to respond.

With that said, I've attempted to add the code to a column and all I get returned is blank. I've changed the code as well to Tier 2 for the second column and still nothing. Below is a picture, though I've whited out names for security reasons. Perhaps there's something I'm missing? The counts in PQ are whole numbers.

[richbenmintz]

Hi @patri0t82 ,

Assuming you are placing the CR_Data[Tier 1] in the first column of a table visual i think you measure with a slight tweak to use then selectedvalue value function should work

Incidents Count =
CALCULATE(
SUM('Incidents Total'[Count]),
FILTER(
    'Incidents Total',
    'Incidents Total'[Name] = selectedvalue('CR_Data'[Tier 1]))
    )
)

Hope this helps

[patri0t82]

Thanks for your help richbenmints. Unfortunately that did not work either. I'm still getting a return of blanks in my CR_Data table. In my visual, I'll place Tier 1 and then the calculated column beside it. When the calculated column is "show values as SUM", the entire visual goes blank. When I set it to "no calculation", I can at least see the names from Tier 1; just no count column.

Here's pictures again of the two tables I'm trying to reference. I've whited out the names, but you'll see what I'm trying to accomplish I think. The top table is Incidents Total and the lower table is CR_Data

[richbenmintz · Accepted solution]

Hi @patri0t82,

I see you are creating a calc column, the following column should work in the CR_Data table

Incidents Count =
var tier = CR_Data'[Tier 1]
return 
CALCULATE(
SUM('Incidents Total'[Count]),
FILTER(
    'Incidents Total',
    'Incidents Total'[Name] = tier)
    )
)

Hope that Helps,

[patri0t82]

Thanks again for the response. I've updated the column with this below, which is just your code tidied up and it's still returning all blanks, with no apparent errors.

Incidents Count =

VAR tier = CR_Data[Tier 1]

RETURN

CALCULATE(

SUM('Incidents Total'[Count]),

FILTER(

'Incidents Total',

'Incidents Total'[Name] = tier)

)

Could it be something to do with field formatting in Power Query?

[richbenmintz]

Hi @patri0t82,

Without looking at your model, it is really hard to say why it is not working, when i create two disconnected tabled and sum up the data from one to the other, it work. Are you able to provide a sample workbook, dummy data is fine as long as the model is the same.

[patri0t82]

Is there any chance that because I have over 3000 of the same name in Tier 1 it's causing PBI problems with calculation?

[richbenmintz]

I cannot imagine that is the case, perhaps you have a relationship between the two tables through aother tables that is not apparent due to bi-directional relationships, just a guess though

[patri0t82]

This response is correct, for what it's worth - I found there actually were relationships causing the problem. Thank you again for all your help.

[patri0t82]

You'll have to forgive me, I'm not sure what the problem is. I tried recreating the solution in a new workbook with dummy data, using the code above and it does work. There must be something elese happening with my file. Your help has been appreciated.