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,

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,

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

        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?