Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Feature that updates calculation based items removed from filter or slicer

I'm looking for a feature in one of my reports and I wanted to check if this was readily available in Power Bi or if it is a way I'm using dax calculation or a slicer.

 

I have at table of order incoming at different dates and a column with the sum of those orders accumulating as they arrive. Exampled below.

 

OrderItemETAAmountTotal at Arrival 
A1AA1/31010
A2AA1/62535
A3AA1/93368

 

Currently im using the DAX: 

 

 

Total on Arrival = 
var maxdate =MAX(table[ETA])
var mindate = TODAY()
return CALCULATE(SUMx(table,table[Amount]),table[ETA] >= mindate, table[ETA] <=maxdate,ALL(table[Order]))

 

 

 

What I would like is a slicer or a feature where I could filter the order and the total on arrival would adjust based on the filtered value. 

 

If I filter order A2, the table would look like this:

OrderItemETAAmountTotal at Arrival 
A1AA1/32525
A3AA1/93358

 

If I filter order A3, the table would look like this:

OrderItemETAAmountTotal at Arrival 
A2AA1/62510
A3AA1/93343

 

Currently when I use the slicer on Order, the calculation doesn't update. 

 

  • tamerj1's avatar
    tamerj1
    2 years ago

    Anonymous 

    It shouldn't make any difference. 
    Probably the the following would work 

    Total on Arrival =
    VAR maxdate =
    MAX ( table[ETA] )
    VAR mindate =
    TODAY ()
    VAR ETAs =
    CALCULATETABLE ( VALUES ( table[ETA] ), ALLSELECTED () )
    RETURN
    CALCULATE (
    SUMX ( table, table[Amount] ),
    FILTER ( ETAs, table[ETA] >= mindate && table[ETA] <= maxdate ),
    ALL ( table[Order] )
    )

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Messed up the tables above

     

    *If I filter order A2, the table would look like this:

    OrderItemETAAmountTotal at Arrival 
    A1AA1/31010
    A3AA1/93343

     

    If I filter order A1, the table would look like this:

    OrderItemETAAmountTotal at Arrival 
    A2AA1/62525
    A3AA1/93358
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous 

    please try

    Total on Arrival =
    VAR maxdate =
    MAX ( table[ETA] )
    VAR mindate =
    TODAY ()
    RETURN
    CALCULATE (
    SUMX ( table, table[Amount] ),
    FILTER (
    ALLSELECTED ( table[ETA] ),
    table[ETA] >= mindate
    && table[ETA] <= maxdate
    ),
    ALL ( table[Order] )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable

      This didn't work for my table, should I be using a slicer to filter out the item or filter using the visual filter?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        Anonymous 

        It shouldn't make any difference. 
        Probably the the following would work 

        Total on Arrival =
        VAR maxdate =
        MAX ( table[ETA] )
        VAR mindate =
        TODAY ()
        VAR ETAs =
        CALCULATETABLE ( VALUES ( table[ETA] ), ALLSELECTED () )
        RETURN
        CALCULATE (
        SUMX ( table, table[Amount] ),
        FILTER ( ETAs, table[ETA] >= mindate && table[ETA] <= maxdate ),
        ALL ( table[Order] )
        )