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...
Show and Tell
BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

Can you please post the sample data/data model if possible.

  • 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

        Thanks. I don't think I have been clear enough! I am trying to add a new measure so that I would have an additional column in the table call ex cash weight. The measure would take the weight per country (which never sums to 100 because of a small % in cash)  it would then use the total weighting and divide each row by the total figure to get a weight excluding cash. 

  • 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.