CODEWITHSUNDEEP
powerbidax
preston
preston

Reputation: 145

Use Slicer selection in calculated column (Parameter)

I have: - a slicer with dates and a table 'Log' with a column 'Date' with dates and a column 'Max' with numbers (e.g. 0,31)

I need to calculate the average of all values in the column 'Max' when the date is before the slicer selection date - and an average for after.

End result: a Card visualisation with the average of all values before the selected date and a Card visualisation with the average of all values after the selected date.

Upvotes: 1

Views: 4866

Answers (2)

Amit
Amit

Reputation: 667

I had to calculate count of all Accounts that were opened before the selected Slicer Date.

Here is what I ended up doing

TotalAccounts = 
CALCULATE(
      DISTINCTCOUNT(Table1.ID),
           FILTER( 
                    ALL(Table1),MIN(Table2.Date)>Table1.Date)
                 )

Upvotes: 0

alejandro zuleta
alejandro zuleta

Reputation: 14108

Create three measures:

avg = AVERAGE('Log'[Max])



AverageAfterSelectedDate =
CALCULATE (
    [avg],
    FILTER ( ALL ( 'Log' ), [Date] > MINX ( 'Log', 'Log'[Date] ) )
)



AverageBeforeSelectedDate =
CALCULATE (
    [avg],
    FILTER ( ALL ( 'Log' ), 'Log'[Date] < MINX ( 'Log', [Date] ) )
)

Just use the AverageBeforeSelectedDate and AverageAfterSelectedDate measures in the cards.

For this data:

enter image description here

It produces:

enter image description here

Let me know if this helps.

Upvotes: 2

Related Questions