Count items in a column based on another column

[Anonymous]

Hi All,

I have calculated the Gross Margin % using Price and Cost value by Item vise.

Every Item having the Upper bound and lower bound, to calculate the Margin violation, as below

My Issue is: I need to count the Items, which Margin% is out of lower and upper bound i.e,

                    count items only if Gross Margin % > UpperBound and Gross Margin% < Lower Bound

                    ignore if the lower and upper bound is blank

Any help there? I'm was searching through community posts but nothing worked for me

Thanks,

[tex628 · Accepted solution]

Try this:

Measure =
SUMX(
SUMMARIZE( 
ItemTable , 
ItemTable[ItemNumber] ,
"Value" , IF( OR([Gross Margin %] < ItemTable[Lower Bound] , [Gross Margin %] > ItemTable[Upper Bound]) , 1 , 0 )
) , [Value]
)

/ J

[Anonymous]

Hi @tex628,

Thank you so much for the solution, It works for me.

Thanks,

Atul

[amitchandak]

@Anonymous , you can have two what if parameter Lower and upper

and create a measure like 

measure 1 = countx(filter(summarize(table, table[Itemnumber], "_1", [Gross Margin %]), [_1] >=selectedvalue(Lower[Lower]) && [_1] <=selectedvalue(upper[upper])),[Itemnumber])

or static value

Measure 2 = countx(filter(summarize(table, table[Itemnumber], "_1", [Gross Margin %]), [_1] >=.1 && [_1] <=.2),[Itemnumber])

what-if

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[Anonymous]

Hi @amitchandak,

Thank you for the solution, but it only shows the Blank every time

Thanks,

Atul