cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

IF SUMMARY Dax

Hell all

 

I am trying to create an IF statement formula to summary results of my cost categories and return me either a TRUE or FALSE.

The syntax should be something like the following

 

IF SUMMARY OF COST CATEGORY 1 IS NOT BETWEEN 10,000 AND -10,000 RETURN A TRUE, IF IT IS BETWEEN 10,000 AND -10,000 RETURN A FALSE.

I would need this to be done for all my available cost categories which are changing though from month to month.

 

Thanks

fanis

 

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @fanisgeorg ,

 

Normally we can use like 

 

calculate(sum(value),allexcept(table,table[category]))

 

to get the value group by different category then use IF() funtion to achieve the purpose.

But i'm not sure what you mean "which are changing though from month to month".

Can you explain this to us and share some sample data if you don't have any Confidential Information?

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi @fanisgeorg ,

 

Normally we can use like 

 

calculate(sum(value),allexcept(table,table[category]))

 

to get the value group by different category then use IF() funtion to achieve the purpose.

But i'm not sure what you mean "which are changing though from month to month".

Can you explain this to us and share some sample data if you don't have any Confidential Information?

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Super User IV
Super User IV

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Second, maybe something like this:

Measure =
  VAR __Value = SUM('Table'[Column])
RETURN
  IF(__Value >= (0-10000) && __Value <= 10000,FALSE(),TRUE())

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @Greg_Deckler and @v-jayw-msft.

Some further details to what I am looking for is.

In the below table only the Personnel costs and the temps Costs are not within the -10K and 10K range. So for these 2 categories I want to return a "true" and for the travel to return a "false".

It has to be dynamic so if next month there is another category and another and another, I need to be captured to the calculation

Thanks a lot for your help

Fanis

 

 

 

Cost CategoryVariance YTD
Personnel8000
Temp-7000
Travel1000
Personel3000
Temp-4000
Travel1000

 

Hello @Greg_Deckler , @v-jayw-msft 

 

I started working on the solution you provided. It seems that I have other parameters to consider that make it more complicated.

Please dont spend your time on providing a solution. Thanks a lot again for the help

 

Tx

F

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors