Forum Discussion

ruicasaisalves's avatar
ruicasaisalves
Regular Visitor
5 years ago
Solved

Hide average when slicer not selected

Hello,

 

I need to show the number of requests done, when no user selected on slicer, but when a user is selected, i need to show the number of requests done vs average by user.

 

I tried using :

 

Maker avg =
VAR Users =
    CALCULATE ( DISTINCTCOUNT ( Data[Maker] ), ALL ( Data ) ) -- distinct users
VAR totalLines =
    CALCULATE ( COUNTROWS ( Data ), ALL ( Data ) ) -- distinct requests
VAR filtercheck =
    ISFILTERED ( Tabela[User] ) -- see if the table has one filter             
VAR averagemaker = totalLines / Users -- requests\user = average requests by user
VAR averageif =
    IF ( Data[Maker No] = 0, BLANK (), averagemaker ) -- dont show average to users with no requests  
VAR test1 =
    IF ( filtercheck, averageif, BLANK () ) -- hide filters when we have no user selection
RETURN
    test1

 

 

But despite the slicer is active or not, the average is always displayed.

 

How can i show only the value of requests done, when no filter is selected:

 

And requests done vs average, when a user is selected ?

Many thanks for the help in advance.

 

Regards,

Rui

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    ruicasaisalves , Change the condition like this and try

    ISFILTERED ( Tabela[User] ) && calculate(count( Tabela[User] ) , allselected(Tabela)) <> calculate(count( Tabela[User] ) , all(Tabela))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    ruicasaisalves , Change the condition like this and try

    ISFILTERED ( Tabela[User] ) && calculate(count( Tabela[User] ) , allselected(Tabela)) <> calculate(count( Tabela[User] ) , all(Tabela))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  ruicasaisalves  ,

    According to your description, I create this data：

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _average_by_user=CALCULATE(AVERAGE('Table'[Maker ]),FILTER(ALL('Table'),'Table'[User]=MAX('Table'[User])))
var _number_of_requests=CALCULATE(SUM('Table'[Maker ]))
return IF(ISFILTERED('Table'[User]),_average_by_user,_number_of_requests)

    2. Result

    If the slicer is not selected, the_ number_ Of_ Requests, if selected, is displayed_ average_ By_ user

    You can downloaded PBIX file from here.

     

    If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.