Forum Discussion

Siddharth_07's avatar
Siddharth_07
Frequent Visitor
4 years ago
Solved

Calculate net asset value

I have a table of asset and market value.  My objective is to calculate the Gross asset and net asset. Data looks like 

Equities123,455,595
Fixed Income222,444,699
Cash23,458,344
Loan-123,567,455

 

Wanted output like this

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Siddharth_07 ，

    According to your description, I create three measures to calculate the gross asset, liabilities and net asset, you can put them into separate visuals to display. In your sample, If there are other items that are not listed, they can be added to the parentheses after IN in the corresponding formula according to their asset-liability attributes.

    Gross asset =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] IN { "Cash", "Equitiea", "Fixed Income" }
    ),
    'Table'[Value]
)
    Liabilities = SUMX(FILTER(ALL('Table'),'Table'[Type]IN{"Loan"}),'Table'[Value])
    Net asset = 'Table'[Gross asset]+'Table'[Liabilities]

     

    I attach my sample bellow for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Siddharth_07 ，

    According to your description, I create three measures to calculate the gross asset, liabilities and net asset, you can put them into separate visuals to display. In your sample, If there are other items that are not listed, they can be added to the parentheses after IN in the corresponding formula according to their asset-liability attributes.

    Gross asset =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] IN { "Cash", "Equitiea", "Fixed Income" }
    ),
    'Table'[Value]
)
    Liabilities = SUMX(FILTER(ALL('Table'),'Table'[Type]IN{"Loan"}),'Table'[Value])
    Net asset = 'Table'[Gross asset]+'Table'[Liabilities]

     

    I attach my sample bellow for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Siddharth_07's avatar
      Siddharth_07
      Frequent Visitor
      4 years ago

       

       

      I tried your solution v-yanjiang-msft but it is not changing for dynamic data. Below I have attached a table in which Valuation date,cust account no,portfolio no are in slicers. So gross assets should change if i choose from slicer for example i need to view gross asset(cash+equities+fixed income+alternatives) for customer account a . So it should show me gross assets same for loans(liabilites) and net asset= gross assets-loan.

       

       

      and we show output as shown in image instead of current month it can be current date eg:( 1/6/2021), previous date(1/5/2021).

      Do attach your sample bellow for reference.

      Best Regards,
      Siddharth Jain

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Siddharth_07 ,

        I'm sorry for that I'm not very clear about your sample, you means there are  Valuation date,cust account no,portfolio no columns in the same table. I don’t understand why different accounts are on the same balance sheet.

        Can you provide a sample that can cover all your data? You can replace the real data.

        Best Regards,
        Community Support Team _ kalyj