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

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

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

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