cancel
Showing results for
Did you mean:
Helper V

## Count items in a column based on another column

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,

1 ACCEPTED SOLUTION
Super User II

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

4 REPLIES 4
Super User II

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

Helper V

Hi @tex628,

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

Thanks,

Atul

Super User IV

@atul-patil , 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

Proud to be a Super User!

Helper V

Hi @amitchandak,

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

Thanks,

Atul

Announcements