Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

SUM with Filter based on a calculation

Hi,   I have a Table with 3 colums: ID, a Number (Nr) and Sales:   ID|Nr |Sales  1|100|$1000  2|101|$1001  3|102|$1000  4|103|$1000  5|100|$1000  6|99 |$1000  7|101|$1002   I would like ...
Need Help
  • Shawn_Fitz's avatar
    Shawn_Fitz
    7 years ago

    Hi Anonymous ,

    Depending on what you need,

    The formula I gave, I'm using it in a measure, but if you use it in a calculated column it won't work.

    If you really need a calculated column, the first create a measure using the formula I gave.

    Then create a calculated column where the column is equal to the measure.

    MeaSalesbyNR = CALCULATE(SUM('Table'[SALES]),FILTER(ALL('Table'), 'Table'[NR] = MAX('Table'[NR]) +1))
    colSalesbyNR = [MeaSalesbyNR]

Shawn_Fitz's avatar
Shawn_Fitz
Frequent Visitor
7 years ago

Hi Anonymous ,

Depending on what you need,

The formula I gave, I'm using it in a measure, but if you use it in a calculated column it won't work.

If you really need a calculated column, the first create a measure using the formula I gave.

Then create a calculated column where the column is equal to the measure.

MeaSalesbyNR = CALCULATE(SUM('Table'[SALES]),FILTER(ALL('Table'), 'Table'[NR] = MAX('Table'[NR]) +1))
colSalesbyNR = [MeaSalesbyNR]

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks so much - sorry not to clarify that I needed a column!

 

What I also came up with is this solution for a column:

 

SalesByNr = 
var CurrentNr = Tabelle1[nr]
return
CALCULATE(SUM(Tabelle1[sales]);FILTER(ALL('Tabelle1'); Tabelle1[nr] = CurrentNr +1))

This gives me the same result. 

In your opinion: are there any drawbacks to this solution?

 

Thanks

Chris

  • Shawn_Fitz's avatar
    Shawn_Fitz
    Frequent Visitor
    7 years ago

    I think the formula you found is even better, since its processing time will be faster than the method I provided.