cancel
Showing results 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"
)
)
))

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

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
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

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"
)``````

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  