cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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
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
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
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
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