cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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
Super User
Super User

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors