Use Slicer selection in calculated column (Parameter)

Question

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.

I think you don't need the calculated column. In you question says you have two, both called Log. Add some sample data and your expected result — alejandro zuleta
– alejandro zuleta, Commented Sep 7, 2016 at 13:31

alejandro zuleta · Accepted Answer · 2016-09-07 18:42:10Z

2

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:

It produces:

Let me know if this helps.

answered Sep 7, 2016 at 18:42

alejandro zuleta

14.1k4 gold badges34 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

preston Over a year ago

I cannot make it work: The formula editor won't let me select the field with the date used in the slicer. I can select fields from the Log table and calculated columns/measures in the SharePoint table (where the slicer date is), but not the field I need. The slicer date is in another table than the Log.

preston Over a year ago

I moved som stuff around and made it work. Thanks a lot :)!

preston Over a year ago

A sidequestion... I have two more slicers active on the same db. These two do not affect the averages at the moment. Is it possible to extend the solution to include more slicers?

Community · Accepted Answer · 2018-03-19 11:34:47Z

0

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)
                 )

edited Mar 19, 2018 at 11:34

CommunityBot

11 silver badge

answered Mar 17, 2017 at 19:37

Amit

6776 silver badges8 bronze badges

Collectives™ on Stack Overflow

Use Slicer selection in calculated column (Parameter)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related