Sub Totals

[Anonymous]

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

[BhaveshPatel]

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

[Anonymous]

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. 

[Anonymous]

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]

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]

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]

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

[Anonymous]

In order to work out the ex cash position I need to take the total weight 95.73% in the example provided and then divide each country weight by 0.9573 to calculate what each country weight is with the cash percentage removed. In the screen shot provided  I would need to divide 1.38% by 0.9573