IF-CALCULATE

[Anonymous]

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. 

[BIReel]

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⚡

[amitchandak]

@Anonymous , see you are lloking for segmentation , refer

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[harshnathani]

HI @Anonymous ,

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)

[az38]

Hi @Anonymous 

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

[kentyler]

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/