Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tyxanu
Helper I
Helper I

IF SUM dax issue

Hi,

 

Can someone help me with this situation I am confronting with?

 

Based on the raw data on the left, I need to reach to the result on the right, but IF(SUM) is not evaluating correctly. What I am missing? What is the right DAX to properly evaluate my condition? (I need to see if the sum is above/equal/below a certain threshold I will define).

issue.PNG

 

Thanks!

1 ACCEPTED SOLUTION

Hi @tyxanu ,

I think you already have your own answer.  

DataNinja777_0-1717076784135.png

You can create the matrix like above from what you already have.  

Best regards,

 

View solution in original post

12 REPLIES 12
DataNinja777
Super User
Super User

Hi @tyxanu ,

What about writing a table formula like below to produce your required output?  

DataNinja777_0-1716983427012.png

I attach an example pbix file.  

Best regards,

@DataNinja777 ,

 

Seems I am getting this error now 

tyxanu_0-1716984305890.png

Am I doing something wrong?

Hi @tyxanu 

That formula is table function so please click "New Table" as shown in the yellow highlighted circle below:

DataNinja777_0-1716984454613.png

Best regards,

@DataNinja777 ,

 

It is working fine (picture below), but I have one last concern 😊.

 

Isn't there any way of "grouping" the data inside my initial table? Because I have many other related necessary data inside there that I need to link & use to create filters/legends. The SUMMARIZE table you've just shown me is being created separately from my data.

 

tyxanu_1-1716985726346.png

 

 

Hi @tyxanu 

Does the below output meet your requirement?  I've shown the total by fruit in a separate calculated column for an easier visualization of the steps taken, instead of doing all in one step which is more abstruct.  I then wrote a simple if formula to separate out "<=15" and the "rest".  

DataNinja777_0-1716987758172.png

I attach an example pbix file. 

Best regards, 

@DataNinja777 ,

 

This one does not help me 😢. Your previous solution in post #7 was the right one. But I would just need to replicate it directly inside the original datasource with all my data. Not as a separate table function. Is this possible?

 

What I am trying to achieve in the end is something like below. However, I need the "Grouping" to be a calculated column that performs the same result, but directly inside my datasource.

tyxanu_2-1716992617808.png

Basically I need your calculation to happen in my datasource:

tyxanu_3-1716992749431.png

 

 

 

Hi @tyxanu 

Both of the output I proposed are basically the same thing, and the 1st output was just the summarized version of the 2nd one.  Did you want to have the same number of rows as your original table?  In that case, I would have imagined that the 2nd output will be closer to your required output.  I am sorry, if I am missing your point.  

Best regards,

Hi @DataNinja777 ,

 

Sorry if I was not very clear.

 

Does this new sample explain what I am trying to achieve? 😊

I just need the same formula that you've shared with me (SUMMARIZE) to work inside my original datasource directly, so I can play with the rest of the columns (Color, Size, etc)

tyxanu_3-1717052097579.png

 

 

 

 

Hi @tyxanu ,

I think you already have your own answer.  

DataNinja777_0-1717076784135.png

You can create the matrix like above from what you already have.  

Best regards,

 

@DataNinja777 ,

 

Many thanks🎉! This one involving EARLIER is the right one. Sorry, I might have missed something, but I've tried it out again and it works perfectly!

 

Regards 

DataNinja777
Super User
Super User

Hi @tyxanu ,

You can create a calculated column like below, to get your required output, but I am wondering why your final output required does not have entry 4.  Is there any particular reason for this?  

DataNinja777_0-1716980615365.png

Best regards,

 

Hi @DataNinja777 ,

 

Exactly, the result you are showing me is exactly my concern, for Apples. For some reason PBi is not aggregating in Apples's case (and other similar entries) and spliting the evaluation into two. I need to display:

 

- Apples -> "rest" (because all the Apple entries add up to "44" which is greater than "15")

 

I need to evaluate whether the aggregation of each type of fruit is greater/equal/less than whatever number I define in the IF formula.

 

PS: my mistake I did not mention that "Count" is a column of numerical values. Those numbers are just numbers, values defined for each entry.

 

Thanks!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors