Help with Matrix row calculations

[Anonymous]

Hello, 

Im trying to make a matrix visual and perform calculations on rows. For example:

Starting Data:

Fee Revenue	500	7/1/2020	Actual
Fee Revenue	1000	7/1/2020	Forecast
Event Driven Fee	200	7/1/2020	Actual
Event Driven Fee	300	7/1/2020	Forecast

Expected Output

Fee Revenue	500	1000
Event Driven Fee	200	300
Recurring Costs	300	700

Now, there is much more to the visual and other calculations that need to occur, but this is an example of one of them. How can i achieve this within a matrix visual?

Thanks for the help!

[Anonymous]

@vanessafvg Hi, Thanks for the quick reply! Unfortunately power BI will not allow me to make that measure part of the columns. Also, wouldnt that give me the calculation as part of the column after actual and forecast? i want to display this as a row item.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You can use Row subtotal as Recurring Costs. Change the name of Row subtotal to Recurring Costs and use the following measure in your matrix:

Measure = IF(HASONEVALUE('Table'[Fee Categrory]),SUM('Table'[Fee]), CALCULATE(SUM('Table'[Fee]),FILTER('Table','Table'[Fee Categrory] = "Fee Revenue")) - CALCULATE(SUM('Table'[Fee]),FILTER('Table','Table'[Fee Categrory] = "Event Driven Fee")))

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeDUDk821gdMvtLrP5SCQSsBl1S6tcpweJHMJVx978F6hw?e=rXtgk2

If this post help, please consider accept it as the solution to help other member find it more quickly.

Best Regards,

Dedmon Dai

[vanessafvg]

if you want to hard code the values

actuals = CALCULATE ( SUM ( [ fee revenue] ) - SUM ( [event driven fee] ), fieldname = "Actual" ) 

and the same for forecast. 

[vanessafvg]

example

placing your measure type on the column 

recurring cost =
CALCULATE ( SUM ( [ fee revenue] ) - SUM ( [event driven fee] ) )