Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Measure Help

Hi Experts,

I would like to create 3 DAX measure for below content. Any one please help.

 

1) AUM_MAR : Sum of market_value_usd per Customer_No for latest BusinessDate

2) Bal_Fun : If Fin_Level2= “Funds” than Sum of market_value_in_usd per Customer_No for latest BusinessDate

3) Bal_Dep :  If Fin_Level2= “Deposits and Current Account” than Sum of market_value_in_usd per Customer_No for latest BusinessDate.

 

Thank you.

 

 

Need Help
Show and Tell
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Please try measures like below:

    AUM_MAR =
CALCULATE (
    SUM ( 'Table'[market_value_usd] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Customer_No] ),
        'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
    )
)
    Bal_Fun =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Funds"
        )
    )
    Bal_Dep =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Deposits and Current Account"
        )
    )

    If the problem persists, please provide dummy data based on your data model.https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 

4 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Please try measures like below:

    AUM_MAR =
CALCULATE (
    SUM ( 'Table'[market_value_usd] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Customer_No] ),
        'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
    )
)
    Bal_Fun =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Funds"
        )
    )
    Bal_Dep =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Deposits and Current Account"
        )
    )

    If the problem persists, please provide dummy data based on your data model.https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Anonymous 

    could you pls provde the sample data and expected output?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    ryan_mayu 

     

    This data is private. Not able to share it. If you able to share the DAX for it. It will be great. Thank you

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      5 years ago

      Anonymous 

      could you pls provide some dummy data?