cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patri0t82
Helper III
Helper III

SUM Column from one table based on value from another table

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!

1 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,





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
Karlos
Super User I
Super User I

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. 




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 No_Results.pngin PQ are whole numbers.

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

 

Picture1.png

 

Should look like.png

 

 

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,





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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?

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors