Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
David_DLLIN
Frequent Visitor

How to use Dax to calculate count of good supplier

Hello, 

I have a table as below. What I want is to set up a date slicer, when I choose a date range, Power BI can automatically show the count of good suppliers. This is dynamic, and my initial idea is to create a new measure, but I cannot figure out, wish someone can give me some idea.

 

  1. Rate = (sum[A]+sum[B])/(sum[A]+sum[B]+sum[C])
  2. Condition for good supplier is rate >80%. 

For example, when date is set from 2020/9/21 to 2021/10/15, count of good suppliers is 2 (SUPPLIER_NO 4 has rate lower than 80%)

 

SUPPLIER_NODATEABC
22020/9/21100
22020/10/16100
22020/12/31100
22021/5/13010
22021/6/18100
22021/8/6010
22021/9/9001
22021/10/15100
32020/9/21100
32020/12/22020
32021/2/25101
32021/10/15020
42020/9/21201
42020/5/26300
42021/3/19200
42021/3/25001
42021/3/29001
42021/5/13001
42021/5/7001
42021/5/11100
42021/10/15100

 

1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

Hi @David_DLLIN 

Here's a measure to get good supplier count

Good Supplier Count = 
SUMX(
    VALUES('Table'[SUPPLIER_NO]),
    VAR _Rate = CALCULATE(
                    DIVIDE(
                        SUM('Table'[A]) + SUM('Table'[B]),
                        SUM('Table'[A]) + SUM('Table'[B]) + SUM('Table'[C]) ) )
    RETURN
        IF(_Rate > 0.8, 1, 0)
)

View solution in original post

2 REPLIES 2
PaulOlding
Solution Sage
Solution Sage

Hi @David_DLLIN 

Here's a measure to get good supplier count

Good Supplier Count = 
SUMX(
    VALUES('Table'[SUPPLIER_NO]),
    VAR _Rate = CALCULATE(
                    DIVIDE(
                        SUM('Table'[A]) + SUM('Table'[B]),
                        SUM('Table'[A]) + SUM('Table'[B]) + SUM('Table'[C]) ) )
    RETURN
        IF(_Rate > 0.8, 1, 0)
)

Thank you @PaulOlding!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors