Forum Discussion
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.
| Order | Item | ETA | Amount | Total at Arrival |
| A1 | AA | 1/3 | 10 | 10 |
| A2 | AA | 1/6 | 25 | 35 |
| A3 | AA | 1/9 | 33 | 68 |
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:
| Order | Item | ETA | Amount | Total at Arrival |
| A1 | AA | 1/3 | 25 | 25 |
| A3 | AA | 1/9 | 33 | 58 |
If I filter order A3, the table would look like this:
| Order | Item | ETA | Amount | Total at Arrival |
| A2 | AA | 1/6 | 25 | 10 |
| A3 | AA | 1/9 | 33 | 43 |
Currently when I use the slicer on Order, the calculation doesn't update.
Anonymous
It shouldn't make any difference.
Probably the the following would workTotal 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
- AnonymousNot applicable
Messed up the tables above
*If I filter order A2, the table would look like this:
Order Item ETA Amount Total at Arrival A1 AA 1/3 10 10 A3 AA 1/9 33 43 If I filter order A1, the table would look like this:
Order Item ETA Amount Total at Arrival A2 AA 1/6 25 25 A3 AA 1/9 33 58 - tamerj1
Community 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] )
)- AnonymousNot 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
Community Champion
Anonymous
It shouldn't make any difference.
Probably the the following would workTotal 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] )
)