cancel
Showing results for
Did you mean:
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_NO DATE A B C 2 2020/9/21 1 0 0 2 2020/10/16 1 0 0 2 2020/12/31 1 0 0 2 2021/5/13 0 1 0 2 2021/6/18 1 0 0 2 2021/8/6 0 1 0 2 2021/9/9 0 0 1 2 2021/10/15 1 0 0 3 2020/9/21 1 0 0 3 2020/12/22 0 2 0 3 2021/2/25 1 0 1 3 2021/10/15 0 2 0 4 2020/9/21 2 0 1 4 2020/5/26 3 0 0 4 2021/3/19 2 0 0 4 2021/3/25 0 0 1 4 2021/3/29 0 0 1 4 2021/5/13 0 0 1 4 2021/5/7 0 0 1 4 2021/5/11 1 0 0 4 2021/10/15 1 0 0

1 ACCEPTED SOLUTION
Super User

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)
)``````
2 REPLIES 2
Super User

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)
)``````
Frequent Visitor

Thank you @PaulOlding!

Announcements