Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Sub Totals

I have a table which shows our monthly allocation to bonds in different countries over the 18 months. I am trying to subtotal the page by month to calculate the total weight in percent for each month. I then need to use this subtotal figure to calculate the weighting to bonds by excluding the cash weighting. Could someone help me with syntax for this please

Show and Tell

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks very much Bhavesh. As the files are very wide I have just provided a screen shot from the excel spread sheet which will hopefullly give you a sense of my problem. I have a file with data which captures the portfolio position at the end of each month. I need to calculate in PowerBI what the total weight to all bonds is in the portfolio in each country  at the end of each month but because of a small cash position this will never add to 100%, so I need to calculate what the ex cash position is by multiplying each bond's weight by the total ex cash position. I can't work out what syntax I should use to calculate this total weight to bonds is at the end of each month and then what syntax I use to calculate the ex cash position. Thanks in advance. 

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi Anonymous,

        In your given screenshot, which column can be used to display the ex cash position? The weight is used to calculate the percent for each contry in each month, right?

        You'd better create a calculated column,  can create measure like the following formula.

        Month=MONTH(Table[Valuation Date])

        Measure:=CALCULATE(SUM(Table[Weight]),ALLEXCEPT(Table[Country of risk]),ALLEXCEPT(Table[Month]))

        Best Regards,
        Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Here is an easier version of what I am trying to do. I have a total allocation to bonds in each country which totals 95.73%, the remaining 4.27% is cash. I need to divide each position by 0.9573 to work out the position excluding cash. I can't work out what syntax will get the correct answer.