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
Icon for Super User rankSuper 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
    Icon for Microsoft Employee rankMicrosoft 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. 

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

        Hi Anonymous,

        The solution given above is a sample. How do you identify the extral cash weigh? According to the screenshot, only the weight column can be used to calculate the weight. And I don't understand the mean"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 will apprecite if you share more details. Thanks a lot.

        Best Regards,
        Angelia