cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
singhpinky123
Frequent Visitor

Show all the stores where response rate is average of it

I have Stores and corresponding Response Rate,i want to show all the Stores where Response Rate is greater than the average of Response Rate
i.e. average of Response rate=0.43 , then Store 2 and Store 3 should be display on  the map.
I tried 

Measure = COUNTROWS(FILTER('Table','Table '[Response Rate]>AVERAGE('Table '[Response Rate])))
which give the count but i need to show the store.
How to implement that?
StoreResponse Rate
10.1
20.5
30.7
1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @singhpinky123 ,

 

Try this:

Measure 2 = 
VAR Avg_ =
    CALCULATE (
        DIVIDE ( SUM ( 'Table'[Response Rate] ), DISTINCTCOUNT ( 'Table'[Store] ) ),
        ALLSELECTED ( 'Table' )
    )
RETURN
    IF ( SUM ( 'Table'[Response Rate] ) > Avg_, 1, 0 )

avg.JPG

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

 

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @singhpinky123 ,

 

Try this:

Measure 2 = 
VAR Avg_ =
    CALCULATE (
        DIVIDE ( SUM ( 'Table'[Response Rate] ), DISTINCTCOUNT ( 'Table'[Store] ) ),
        ALLSELECTED ( 'Table' )
    )
RETURN
    IF ( SUM ( 'Table'[Response Rate] ) > Avg_, 1, 0 )

avg.JPG

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

 

View solution in original post

camargos88
Super User III
Super User III

@singhpinky123 ,

 

You want to count the number os stores above the average ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



camargos88
Super User III
Super User III

@singhpinky123 ,

 

You can use this measure and filter the visual:

Measure = IF(SUM('Table'[Response Rate]) > CALCULATE(AVERAGE('Table'[Response Rate]), ALL('Table'[Store])), 1, 0)


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88 This solution works but i want the average as a contant.i.e. if i have 100 of stores then it will do average of 100 of stores,then it will show the stores which is greater than average of the Response Rate.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.