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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors