topic Re: DAX formula to count number of value base on measure in DAX Commands and Tips

DAX formula to count number of value base on measure

Dimitri70 — Fri, 28 Mar 2025 03:16:42 GMT

Hello, I'm getting very frustating today trying to make a new measure but not giving correct result.

I have this table

Commodity	Supplier	Part number	Inventory	Past consumption
x	1	xx1	10	1
x	2	xx2	10	5
y	1	xx3	10	400
y	2	xx4	10	1000
y	3	xx5	10	200
z	1	xx6	10	300
z	4	xx7	10	2

In power BI, I created a new measure to calculate the Inventory turn over ITO =(Past consumption /Inventory) and then I would like to create a formula to know for example when I select the commodity X, how many supplier has the Inventory turn value less than 12 (answer should be 2). And if I select commodity X and Y, it should still be 2.

I tried to use a formula such as = CALCULATE(DISTINCTCOUNT('data'[Supplier]),FILTER('data','data'[ITO]<12)) but result is not matching.

Do you have an idea how I could proceed?

Thank you

Re: DAX formula to count number of value base on measure

Jihwan_Kim — Fri, 28 Mar 2025 03:54:23 GMT

Hi,

I am not sure how it is created, but if you try to create calcualted column like the below, I think it is working.
please check the below picture and the attached pbix file.

this is calculated column

ITO CC = data[Past consumption] / data[Inventory]

this is measure.

expected result measure: = CALCULATE ( DISTINCTCOUNT ( 'data'[Supplier] ), 'data'[ITO CC] < 12 )

Re: DAX formula to count number of value base on measure

Dimitri70 — Fri, 28 Mar 2025 04:10:59 GMT

Thank you for the suggestion, I tried but I get same result.

from the moment where a supplier has a part number in the commodity with the ITO<12, it is count as 1 even if the total value of ITO per supplier &commodity is >12.

Re: DAX formula to count number of value base on measure

Jihwan_Kim — Fri, 28 Mar 2025 04:17:11 GMT

Hi,

Please share your sample pbix file's link (onedrive or dropbox or googlelink or else) here, and then I can try to look into it.

Thank you.

Re: DAX formula to count number of value base on measure

Dimitri70 — Fri, 28 Mar 2025 05:02:54 GMT

Sorry I cant sent as it is sensitive data.

But below this is to show my issue:

You can see below at supplier level ITO is 43 so higher than 12 but formula still give 1 as answer

Then at supplier and commodity level: Still formula give 1 even if ITO is higher than 12

By deepdown I can see the 1 is coming because 1 part number.

Is there a way to considere supplier level?

Re: DAX formula to count number of value base on measure

venkatesh-11 — Fri, 28 Mar 2025 07:41:03 GMT

Hello

,

Please find below my solution regarding your ask please use this

Solution =
var table_ = ADDCOLUMNS(VALUES(solution[Commodity]),"Div",CALCULATE(DIVIDE( SUM(solution[Past consumption]),SUM(solution[Inventory]))))

RETURN
CALCULATE(DISTINCTCOUNT(solution[Supplier]),FILTER(table_,[Div] <12))

Re: DAX formula to count number of value base on measure

venkatesh-11 — Fri, 28 Mar 2025 07:43:00 GMT

Solution =
var table_ = ADDCOLUMNS(VALUES(solution[Commodity]),"Div",CALCULATE(DIVIDE( SUM(solution[Past consumption]),SUM(solution[Inventory]))))

RETURN
CALCULATE(DISTINCTCOUNT(solution[Supplier]),FILTER(table_,[Div] <12))

Re: DAX formula to count number of value base on measure

venkatesh-11 — Fri, 28 Mar 2025 08:10:40 GMT

Hi Dimitri Please refer my solution if its works please give thumbs up
Solution =
var table_ = ADDCOLUMNS(VALUES(solution[Commodity]),"Div",CALCULATE(DIVIDE( SUM(solution[Past consumption]),SUM(solution[Inventory]))))

RETURN
CALCULATE(DISTINCTCOUNT(solution[Supplier]),FILTER(table_, [Div] <12))

Re: DAX formula to count number of value base on measure

venkatesh-11 — Fri, 28 Mar 2025 08:11:11 GMT

RETURN
CALCULATE(DISTINCTCOUNT(solution[Supplier]),FILTER(table_, [Div] <12))

Re: DAX formula to count number of value base on measure

venkatesh-11 — Fri, 28 Mar 2025 08:11:48 GMT

RETURN
CALCULATE(DISTINCTCOUNT(solution[Supplier]),FILTER(table_, [Div] <12))

Re: DAX formula to count number of value base on measure

Dimitri70 — Fri, 28 Mar 2025 12:54:36 GMT

Thank you for the proposal, it almost work as I expext except that the formula count how many time for each supplier and commodities the ITO is lower than 12, so if supplier is serving 3 commodities that are lower than 1, it will count for 3.

Is there possible to check just the ITO at supplier level?

from

to this

Even if supplier has ITO per commodity <12 just considering the total ITO for the all ommodity in once.

Thank you very much for your support