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",
Bal <= 2000, "$1K - $2K",
Bal <= 50000, "$2K - $5K",
"$5K+"
)


Method 2

Member Balance (Selected Product2) =
VAR _snap = SELECTEDVALUE('custdataMLY'[Snapshot Date])
VAR _ptype = SELECTEDVALUE('custdataMLY'[Product Type])
RETURN
COALESCE(
CALCULATE(
SUM('custdataMLY'[Balance Amount]),
ALLEXCEPT(
'custdataMLY',
'custdataMLY'[custID],
'custdataMLY'[Snapshot Date]
),
KEEPFILTERS('custdataMLY'[Snapshot Date] = _snap),
KEEPFILTERS('custdataMLY'[Product Type] = _ptype),
KEEPFILTERS( ISBLANK('custdataMLY'[CustmembershipClosedDate]) )
),
0
)


Members in Tier (Selected Product) =
VAR _tier = SELECTEDVALUE( DimBalanceTier[Tier] )
VAR _members =
VALUES ( 'custdataMLY'[custID] )
RETURN
COUNTROWS(
FILTER(
_members,
[Member Tier (Selected Product)] = _tier
)
)

Member Tier (Selected Product) =
VAR Bal = [Member Balance (Selected Product2)]
RETURN
SWITCH (
TRUE(),
Bal <= 0, "<=$0",
Bal <= 100, "$0-$100",
Bal <= 500, "$100 - $500K",
Bal <= 1000, "$500 - $1K",
Bal <= 2000, "$1K - $2K",
Bal <= 50000, "$2K - $5K",
"$5K+"
)



Method 3

Member Deposit Balance =
VAR _snap =
SELECTEDVALUE ( 'custdataMLY'[Snapshot Date] )
RETURN
COALESCE (
CALCULATE (
SUM ( 'custdataMLY'[Balance Amount] ),

-- ✅ preserve MEMBER grain
ALLEXCEPT (
'custdataMLY',
'custdataMLY'[CustId],
'custdataMLY'[Snapshot Date]
),

-- ✅ hard filter to Deposit ONLY
'custdataMLY'[Product Type] = " Corporate",

-- ✅ snapshot safety
'custdataMLY'[Snapshot Date] = _snap,

-- ✅ open accounts only
ISBLANK ( 'custdataMLY'[CustmembershipClosedDate] )
),
0
)

Member Deposit Tier =
VAR Bal = [Member Deposit Balance]
RETURN
SWITCH (
TRUE(),
Bal <= 0, "<=$0",
Bal <= 100, "$0-$100",
Bal <= 500, "$100 - $500K",
Bal <= 1000, "$500 - $1K",
Bal <= 2000, "$1K - $2K",
Bal <= 50000, "$2K - $5K",
"$5K+"
)

we have customer ata who has many products subtypes all classified as Product type
i need to hav balance tiers created
in the selected snapshot and selected product type i need to shpw below


each customer should be assigned one tier based on teh product selection, i tried a lot of cals but it didnt work

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",
Bal <= 2000, "$1K - $2K",
Bal <= 50000, "$2K - $5K",
"$5K+"
)


Method 2

Member Balance (Selected Product2) =
VAR _snap = SELECTEDVALUE('custdataMLY'[Snapshot Date])
VAR _ptype = SELECTEDVALUE('custdataMLY'[Product Type])
RETURN
COALESCE(
CALCULATE(
SUM('custdataMLY'[Balance Amount]),
ALLEXCEPT(
'custdataMLY',
'custdataMLY'[custID],
'custdataMLY'[Snapshot Date]
),
KEEPFILTERS('custdataMLY'[Snapshot Date] = _snap),
KEEPFILTERS('custdataMLY'[Product Type] = _ptype),
KEEPFILTERS( ISBLANK('custdataMLY'[CustmembershipClosedDate]) )
),
0
)


Members in Tier (Selected Product) =
VAR _tier = SELECTEDVALUE( DimBalanceTier[Tier] )
VAR _members =
VALUES ( 'custdataMLY'[custID] )
RETURN
COUNTROWS(
FILTER(
_members,
[Member Tier (Selected Product)] = _tier
)
)

Member Tier (Selected Product) =
VAR Bal = [Member Balance (Selected Product2)]
RETURN
SWITCH (
TRUE(),
Bal <= 0, "<=$0",
Bal <= 100, "$0-$100",
Bal <= 500, "$100 - $500K",
Bal <= 1000, "$500 - $1K",
Bal <= 2000, "$1K - $2K",
Bal <= 50000, "$2K - $5K",
"$5K+"
)

 

Method 3

Member Deposit Balance =
VAR _snap =
SELECTEDVALUE ( 'custdataMLY'[Snapshot Date] )
RETURN
COALESCE (
CALCULATE (
SUM ( 'custdataMLY'[Balance Amount] ),

-- ✅ preserve MEMBER grain
ALLEXCEPT (
'custdataMLY',
'custdataMLY'[CustId],
'custdataMLY'[Snapshot Date]
),

-- ✅ hard filter to Deposit ONLY
'custdataMLY'[Product Type] = " Corporate",

-- ✅ snapshot safety
'custdataMLY'[Snapshot Date] = _snap,

-- ✅ open accounts only
ISBLANK ( 'custdataMLY'[CustmembershipClosedDate] )
),
0
)

Member Deposit Tier =
VAR Bal = [Member Deposit Balance]
RETURN
SWITCH (
TRUE(),
Bal <= 0, "<=$0",
Bal <= 100, "$0-$100",
Bal <= 500, "$100 - $500K",
Bal <= 1000, "$500 - $1K",
Bal <= 2000, "$1K - $2K",
Bal <= 50000, "$2K - $5K",
"$5K+"
)

either i get duplicate records of tiers or the numbers dont line up, can you please help.
 Ihave attached the data.

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

     

     

     

14 Replies

  • Swathykorivi's avatar
    Swathykorivi
    Helper I
    4 months ago
    snapshotdate	Custid	Product type	Product subtype	Productsid	Balance
1/31/2026	23531A	Consumer	Office Supplies	103800	 $2,396.40 
1/31/2026	23531A	Home Office	Office Supplies	112326	 $3,406.66 
1/31/2026	23531A	 Corporate	Technology	141817	 $22,638.48 
1/31/2026	23531A	 Corporate	Furniture	167199	 $17,499.95 
2/28/2026	23531A	Home Office	Office Supplies	112326	 $13,999.96 
2/28/2026	23531A	 Corporate	Technology	141817	 $11,199.97 
2/28/2026	23531A	 Corporate	Furniture	167199	 $10,499.97 
3/31/2026	23531A	Consumer	Office Supplies	118192	 $9,892.74 
3/31/2026	23531A	Consumer	Office Supplies	162775	 $9,449.95 
3/31/2026	23531A	Home Office	Office Supplies	112326	 $9,099.93 
3/31/2026	23531A	 Corporate	Technology	141817	 $8,749.95 
3/31/2026	23531A	 Corporate	Furniture	167199	 $8,399.98 
1/31/2026	292381A	Consumer	Office Supplies	169971	 $8,187.65 
1/31/2026	292381A	Home Office	Office Supplies	169978	 $8,159.95 
1/31/2026	292381A	 Corporate	Technology	169999	 $7,999.98 
1/31/2026	292381A	 Corporate	Furniture	174514	 $272.74 
2/28/2026	292381A	Home Office	Office Supplies	169978	 $4,799.98 
2/28/2026	292381A	 Corporate	Furniture	174514	 $4,663.74 
3/31/2026	292381A	Consumer	Office Supplies	169978	 $4,643.80 
3/31/2026	292381A	Consumer	Office Supplies	169859	 $4,548.81 
3/31/2026	292381A	Home Office	Office Supplies	169887	 $4,535.98 
3/31/2026	292381A	 Corporate	Technology	169894	 $4,499.99 
3/31/2026	292381A	 Corporate	Furniture	169901	 $4,476.80 
1/31/2026	286511A	Consumer	Office Supplies	167913	 $1,793.98 
2/28/2026	286511A	Consumer	Office Supplies	167913	 $1,781.68 
3/31/2026	286511A	Consumer	Office Supplies	167913	 $1,779.90 
3/31/2026	286511A	 Corporate	Technology	167871	 $1,747.25
  • Swathykorivi's avatar
    Swathykorivi
    Helper I
    4 months ago

    for some reason i couldnt attach the spreadsheet

    snapshotdateCustidProduct typeProduct subtypeProductsidBalance
    1/31/202623531AConsumerOffice Supplies103800 $    2,396.40
    1/31/202623531AHome OfficeOffice Supplies112326 $    3,406.66
    1/31/202623531A CorporateTechnology141817 $  22,638.48
    1/31/202623531A CorporateFurniture167199 $  17,499.95
    2/28/202623531AHome OfficeOffice Supplies112326 $  13,999.96
    2/28/202623531A CorporateTechnology141817 $  11,199.97
    2/28/202623531A CorporateFurniture167199 $  10,499.97
    3/31/202623531AConsumerOffice Supplies118192 $    9,892.74
    3/31/202623531AConsumerOffice Supplies162775 $    9,449.95
    3/31/202623531AHome OfficeOffice Supplies112326 $    9,099.93
    3/31/202623531A CorporateTechnology141817 $    8,749.95
    3/31/202623531A CorporateFurniture167199 $    8,399.98
    1/31/2026292381AConsumerOffice Supplies169971 $    8,187.65
    1/31/2026292381AHome OfficeOffice Supplies169978 $    8,159.95
    1/31/2026292381A CorporateTechnology169999 $    7,999.98
    1/31/2026292381A CorporateFurniture174514 $         272.74
    2/28/2026292381AHome OfficeOffice Supplies169978 $    4,799.98
    2/28/2026292381A CorporateFurniture174514 $    4,663.74
    3/31/2026292381AConsumerOffice Supplies169978 $    4,643.80
    3/31/2026292381AConsumerOffice Supplies169859 $    4,548.81
    3/31/2026292381AHome OfficeOffice Supplies169887 $    4,535.98
    3/31/2026292381A CorporateTechnology169894 $    4,499.99
    3/31/2026292381A CorporateFurniture169901 $    4,476.80
    1/31/2026286511AConsumerOffice Supplies167913 $    1,793.98
    2/28/2026286511AConsumerOffice Supplies167913 $    1,781.68
    3/31/2026286511AConsumerOffice Supplies167913 $    1,779.90
    3/31/2026286511A CorporateTechnology167871 $    1,747.25
  • Swathykorivi's avatar
    Swathykorivi
    Helper I
    4 months ago

    I created calc table and used summarize function to bring one member and snapshot and balance but it didnt work either , the member counts are way off

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


  • mickey64's avatar
    mickey64
    Super User
    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+"

     

     

     

    • Swathykorivi's avatar
      Swathykorivi
      Helper I
      4 months ago

      mickey64 : i tried taht logic too, but it is showing up $5K+ the last tier
      The snapshot date and product type is my slicer
      i am using bar charts in theview where i should have balance tiers and Customer count, the logic is only working if i bring teh custid into the view.

    • Swathykorivi's avatar
      Swathykorivi
      Helper I
      4 months ago

       I need something like this for selected snapshot month and product type

  • v-priyankata's avatar
    v-priyankata
    Community Support
    3 months ago

    Hi Swathykorivi 

    Thank you for reaching out to the Microsoft Fabric Forum Community.

    danextian mickey64 Ashish_Mathur Thanks for the inputs.

    I hope the information provided by users  was helpful. If you still have questions, please don't hesitate to reach out to the community.

     

    • v-priyankata's avatar
      v-priyankata
      Community Support
      3 months ago

      Hi Swathykorivi 

      Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.