DAX formula to count number of value base on measure

[Dimitri70]

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

[venkatesh-11 · Accepted solution]

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

[Dimitri70]

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

[venkatesh-11]

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

[venkatesh-11]

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

[venkatesh-11]

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

[venkatesh-11]

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

[Dimitri70]

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.

[Jihwan_Kim]

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.

[Jihwan_Kim]

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 )

[Dimitri70]

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?