cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
atul-patil
Helper V
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

 

Margin.png

 

 

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
tex628
Super User II
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


Connect on LinkedIn

View solution in original post

4 REPLIES 4
tex628
Super User II
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


Connect on LinkedIn

View solution in original post

Hi @tex628,

 

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

 

Thanks,

Atul

amitchandak
Super User IV
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak,

 

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

 

 

Thanks,

Atul

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors