cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Debasish1011
New Member

Want to find the number of Suppliers with Average Rating between 4 and 5

Hi

 

I have data in the following format:

 

Supplier ID            Rating Value

-------------          ------------------

123                            4

123                            5

234                            1

567                            2

345                            4

234                            4

 

 

I want to find the count of suppliers whose average rating is between 4 and 5.

 

Average rating of a supplier = Sum of Ratings received by the supplier/Total no. of ratings received by the supplier

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Debasish1011

 

Try thsi MEASURE

 

Count_of_Suppliers =
COUNTROWS (
    FILTER (
        ALLSELECTED ( Table1[Supplier ID] ),
        CALCULATE ( AVERAGE ( Table1[Rating Value] ) ) >= 4
            && CALCULATE ( AVERAGE ( Table1[Rating Value] ) ) <= 5
    )
)
Regards
Zubair

Please try my custom visuals


View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

@Debasish1011

 

Try thsi MEASURE

 

Count_of_Suppliers =
COUNTROWS (
    FILTER (
        ALLSELECTED ( Table1[Supplier ID] ),
        CALCULATE ( AVERAGE ( Table1[Rating Value] ) ) >= 4
            && CALCULATE ( AVERAGE ( Table1[Rating Value] ) ) <= 5
    )
)
Regards
Zubair

Please try my custom visuals


View solution in original post

Thanks a lot Zubair, its working

@Zubair_Muhammad

 

Can we help me with another condition please.

 

How to calculate the number of Suppliers with Average Rating between 4 and 5 but will only consider the Suppliers who have got more than one rating count.

@Debasish1011

 

You can add another condition as follows

 

Count Of Suppliers =
COUNTROWS (
    FILTER (
        ALLSELECTED ( Table1[Supplier ID] ),
        CALCULATE ( AVERAGE ( Table1[Rating Value] ) ) >= 4
            && CALCULATE ( AVERAGE ( Table1[Rating Value] ) ) <= 5
            && CALCULATE ( COUNT ( Table1[Rating Value] ) ) > 1
    )
)
Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors