Forum Discussion

Swathykorivi's avatar
Swathykorivi
Helper I
4 months ago
Solved

Need help to create Calculation in DAX

Method 1 BalanceTiers = VAR Bal = COALESCE('custdataMLY'[Balance Amount],0) RETURN SWITCH( TRUE(), Bal <= 0, "<=$0", Bal <= 100, "$0-$100", Bal <= 500, "$100 - $500K", Bal <= 1000, "$500 - $1K", B...
Need Help
  • mickey64's avatar
    mickey64
    4 months ago

    For your reference.

     

    Step 0: I use these data below.

    <DATA>

     

    Step 1: I make a measure and two matrixs.

    Balance Tiers = IF(SUM(DATA[Balance])<= 0, "<=$0",
        IF(SUM(DATA[Balance])<= 100, "$0-$100",
        IF(SUM(DATA[Balance])<= 500, "$100 - $500",
        IF(SUM(DATA[Balance])<= 1000, "$500 - $1K",
        IF(SUM(DATA[Balance])<= 2000,"$1K - $2K",
        IF(SUM(DATA[Balance])<= 5000, "$2K - $5K","$5K+"))))))

     

    By the way, there are many mistype in your text.

    Bal <= 0, "<=$0",
    Bal <= 100, "$0-$100",
    Bal <= 500, "$100 - $500K",
    Bal <= 1000, "$500 - $1K",
    Bal <= 2000, "$1K - $2K",
    Bal <= 50000, "$2K - $5K",
    "$5K+"

     

     

     

danextian's avatar
danextian
Super User
4 months ago

Given the same sample data, what is your expected result and how so?

Swathykorivi's avatar
Swathykorivi
Helper I
4 months ago

the total is supposed to be 113308 which is showing up in the chart, if i export it the % is more than 100% and the total customers are 173K, this is because since there are multiple sub products within the category I see the members are counted twice. 
below is my current logic

BalanceTiers =
VAR Bal = COALESCE('custdataMLY'[BalanceAmount],0)

RETURN

SWITCH(
TRUE(),
Bal <=0, "<=$0",
Bal < 100, "$0-$100",
Bal < 500, "$100 - $500",
Bal < 1000, "$500 - $1K",
Bal < 2000, "$1K - $2K",
Bal < 5000, "$2K - $5K",
Bal < 8000, "$5K - $8K",
Bal < 10000, "$8K - $10K",
"$10K+"
)