cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Applying If statement to column after already aggregated in Table

Hi All,

  Hopefully I’m able to explain this situation clearly, I’m somewhat new.  I have a table like this

Product Code

Quarter

Searches

14753551

2019Q1

150

14753551

2019Q2

200

14753551

2019Q3

300

14753551

2019Q4

200

14754061

2019Q1

600

14754681

2019Q1

1100

 

I have a table in Power BI with Product Code and Sum of Searches as in the picture attached.  I want to apply an If statement AFTER it is already aggregated like the way it happens in an Excel pivot,  since the user can select whatever quarter(s) they want in a slicer. 

   So if the sum of seaches > 400 then should be  ‘yes!’.  But even if I try using calculate, Sumx, it is still considering the Quarter field in the if statement somehow.  The red box I highlighted should be yes!.   It is correct if I bring in the Quarter - see other picture.  Any ideas? Thank you!pbi_sample_wrong.pngpbi_sample_right.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Applying If statement to column after already aggregated in Table

Hi @JLY 

try a measure

Measure = 
if(calculate(sum('Table'[Searches]);ALLEXCEPT('Table';'Table'[Quarter];'Table'[Product Code]))>400;"yes";"no")

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Applying If statement to column after already aggregated in Table

Hi @JLY 

try a measure

Measure = 
if(calculate(sum('Table'[Searches]);ALLEXCEPT('Table';'Table'[Quarter];'Table'[Product Code]))>400;"yes";"no")

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Highlighted
Regular Visitor

Re: Applying If statement to column after already aggregated in Table

Thanks so much!   I just had to change ";" to ","  and exactly what I needed and I would not have figured this out any time soon.  Thanks again

Highlighted
Super User II
Super User II

Re: Applying If statement to column after already aggregated in Table

@JLY the symbol-delimiter depends on system localization.

cheer and good luck! welcome to pbi family 🙂

 

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors