Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create column that sums from another table

I'm a beginner with Power BI and probably havent used whatever function I need that can accomoplish what I need... appreciate any help / support I can get! Thanks in advance!

 

I have a table with data like such:

LetterCount
A3
A2
A4
B4

 

And I have a master mapping file with just letters. I'd like to create a column in this other table that sums the count of the above. I know I can create a measure to sum the counts, but I specifically need it to be in column format so I can create text based columns off of it. I'd like the end result to look something the below. Need the range to also be a column and not measure so it can become a filter.

 

LetterCount (new column to sum off another table)Range (new column formula)
A95-10
B41-5

 

Thank you!! 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    Write this calculated column formula in the Letters table

    Count of letters = calculate(sum(Data[count]),filter(Data,Data[Letter]=earlier(Data[Letter])))

    Hope this helps.

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    Anonymous 

    pls try this

    Column = 
VAR _SUM=CALCULATE( sum('Table'[Count]) ,ALLEXCEPT('Table','Table'[Letter]))
return SWITCH(TRUE(),_SUM<5,"1-5",_SUM<10,"5-10")

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Write this calculated column formula in the Letters table

    Count of letters = calculate(sum(Data[count]),filter(Data,Data[Letter]=earlier(Data[Letter])))

    Hope this helps.