Dividing by Fixed Column in Matrix Issues

[philbert119]

I have a matrix that has months (M1, M2, M3) in the column and different campaigns as rows. What I want to produce is a matrix where I can show how the balances have reduced each month from M02. The formula is below.

Balance Remaining % = DIVIDE([Balances Sum],CALCULATE([Balances Sum],'Trend'[Month]="M02"),0)

It is returning all 0's except for M02 where it is 100% for each campaign.

When I adjusted the formula to add ALL, it fixes this problem, but it breaks up the % by the different campaigns and is not isolated to a single row/campaign like I want. See screenshots below for each scenario. Any advice how I can get this to work for each campaign?

Formulas:

1. Balance Remaining % = DIVIDE([Balances Sum],CALCULATE([Balances Sum],'Trend'[Month]="M02"),0)

Results: Returning 0 for every month except M02 where it returns 100% for all campaigns

2. Balance Remaining % = DIVIDE([Balances Sum],CALCULATE([Balances Sum],ALL('Trend'),'Trend'[Month]="M02"),0)

Results: Returning % for all months, but instead of the % being isolated for that one campaign, it is broken up between the different rows/campaigns. I would like to the % to be for each campaign/row.

Any help would be appreciated.

Thanks!

Phil

Could anyone help out with this one?

[v-luwang-msft]

Hi @philbert119 ,

could pls provide your pbix file  ? And  Show the final result you want to present. Remember to remove the confidential information.

Best Regard

Lucien Wang