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
August 1 episode 9_no_dates 768x460.jpg

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 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors