Forum Discussion

patri0t82's avatar
patri0t82
Icon for Post Patron rankPost Patron
5 years ago
Solved

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!

Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    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,

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      patri0t82
      Icon for Post Patron rankPost Patron
      5 years ago

      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's avatar
        richbenmintz
        Icon for Resident Rockstar rankResident Rockstar
        5 years ago

        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