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

## IF-CALCULATE

Hello All,

I want to filter with rating as per below

Total Sales < 8000  = 1

Total Sales 8000  to 10000 = 2

Total Sales  > 10000 = 3

I have created new column with follwing filter and it is not working.

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

IF( CALCULATE(SUM(Trans[Price.PRICE]),ALLEXCEPT(Trans,Trans[CUSTNUM1])< 8000 , "1",
IF(
CALCULATE(SUM(Trans[Price.PRICE]),ALLEXCEPT(Trans,Trans[CUSTNUM1])) >8000 && <10000, "2",
IF(
CALCULATE(SUM(Trans[Price.PRICE]),ALLEXCEPT(Trans,Trans[CUSTNUM1]))<=10000,"3"
)
)
))

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

Thanks in advance for your reply.

5 REPLIES 5
Highlighted
Super User II

## Re: IF-CALCULATE

When you say "and it's not working", just what is it doing ?

You might consider creating a "helper measure"

Calc_Total = CALCULATE(SUM(Trans[Price.PRICE]),ALLEXCEPT(Trans,Trans[CUSTNUM1])

and put that in your table and see if it's returning the answers you expect.

If so, you can use it to calculate your column.
If you are interested in turning your 3 categories into a slicer, you could use the "parameter table" pattern https://www.daxpatterns.com/parameter-table/

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Highlighted
Super User VI

## Re: IF-CALCULATE

Hi @SWEZIN

try SWITCH()

``````Measure =
var _SumPrice = CALCULATE(SUM(Trans[Price.PRICE]),ALLEXCEPT(Trans,Trans[CUSTNUM1]) )

RETURN
SWITCH(TRUE(),
_SumPrice  < 8000 , "1",
_SumPrice  > 8000 && _SumPrice < 10000, "2",
"3"
)
``````

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Super User V

## Re: IF-CALCULATE

HI @SWEZIN ,

Try this measure

Measure =

var a = CALCULATE(SUM(Trans[Price.PRICE]),ALLEXCEPT(Trans,Trans[CUSTNUM1]

RETURN

SWITCH(

TRUE(),

a >= 10000, "3",

a <10000 && a  >= 8000 ,"2",

"1"

)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Super User IX

## Re: IF-CALCULATE

@SWEZIN , see you are lloking for segmentation , refer

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!

Highlighted
Frequent Visitor

## Re: IF-CALCULATE

``````Measure =
VAR FilteredPrice =
CALCULATE ( SUM ( Trans[Price.PRICE] ), ALLEXCEPT ( Trans, Trans[CUSTNUM1] ) )
RETURN
SWITCH (
TRUE (),
FilteredPrice < 8000, "1",
FilteredPrice > 8000
&& FilteredPrice < 10000, "2",
// default case
"3"
)``````

Christopher Pollock @ LinkedIn

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

Vist us at https://www.bireel.com/ for courses, development and consulting purposes

## Helpful resources

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors