How to use Dax to calculate count of good supplier

David_DLLIN — Fri, 11 Mar 2022 16:36:12 GMT

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.

Rate = (sum[A]+sum[B])/(sum[A]+sum[B]+sum[C])
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

Re: How to use Dax to calculate count of good supplier

PaulOlding — Fri, 11 Mar 2022 17:05:22 GMT

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) )

Re: How to use Dax to calculate count of good supplier

David_DLLIN — Sat, 12 Mar 2022 03:09:39 GMT

Thank you PaulOlding!

topic Re: How to use Dax to calculate count of good supplier in DAX Commands and Tips

How to use Dax to calculate count of good supplier

Re: How to use Dax to calculate count of good supplier

Re: How to use Dax to calculate count of good supplier