How to show row subtotals and values selectively

[Anonymous]

I'm trying to make a matrix chart where I'm displaying sum of total balance for several categories along with total of weighted average(WAC). I want the values to just be total of balance and the grand total of both balance and WAC. In the matrix plot when I add WAC and Balance under values field, it generates values for both the categories, but I just want it for balances.

To be more clear here is a pictorial representation of the problem:

My Current chart is showing this:

And I want to just display the Balances as the values and WAC and Balance as row subtotals like: 

Is there any option in matrix plot to achieve this result, I've tried every option but couldn't find anything. Any advice or alternate approach to achieve this will be highly appreciated.

Additionally, is there any way to get the sub-totals at the beginning rather than at the end. 

Here's the link to pbix file with relavant data: https://drive.google.com/file/d/12z6ltTFckoO_Xb-hMEO6jTtIDhT5VEMM/view?usp=sharing

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can change it to a table, and create a lot of measures:

1. The functions from 1m to 12 + are almost the same. You can change the string in it:

1M =
var _1=
SUMX(FILTER(Sheet1,[Remaining term cat]="1M"), Sheet1[Balance] * Sheet1[RATE])/SUMX(FILTER('Sheet1',[Remaining term cat]="1M"),Sheet1[Balance]
return
IF(_1=BLANK(),0,_1)

 2. Finally, the sum of the weighted average (WAC) can be obtained by adding the functions from 1m to 12 + and dividing by the total

all =
([12+]+[12M]+[1M]+[2M]+[3M]+[4M]+[5M]+[6M]+[7m]+[8M]+[9M]) / 11

 3. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[Anonymous]

Thanks @Anonymous . I did something similar and got the results, your idea is definitely an improved version of mine.

[Anonymous]

Hi  @Anonymous ,

This is how he works, as long as there is a value in value，the entries in that column will repeat

Modify the measure so that the value of the WAC column is blank

WAC =
var _tempwac=SUMX(Sheet1, Sheet1[Balance] * Sheet1[RATE])/SUM(Sheet1[Balance])
return
if(ISINSCOPE('Sheet1'[Remaining term cat]),BLANK(),_tempwac)

Result:

You can downloaded PBIX file from here.

Here's a link to the related content, hoping to help you:：

https://community.powerbi.com/t5/Desktop/Stop-measures-from-repeating-in-matrix-visual/td-p/797500

https://community.powerbi.com/t5/Desktop/Display-a-measure-as-a-column-in-matrix-visual/td-p/740291

Best Regards,

Liu Yang

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

[Anonymous]

Thanks for replying @Anonymous . The WAC column is blank but I wanted the WAC column just as a total and not in the main chart. Is there any other way to achieve this apart from matrix plot?