Matrix Calculations

[TH123]

Hi 

In my matrix table below there are 2 formulas I need to add

1. How do I sum only the months? (I.e January to September) Doing this will provide me with my Flows YTD and this formual should NOT  include the Opening & Closing NAV. This formula should then be displayed between the September column and Closing NAV column.

2. Thereafter I need to have a market movement column, which will be calculated by taking the  Closing NAV less the Flows YTD (calculated in point 1 above) less the Opening NAV

This column should then be displayed between the Flows YTD column and Closing NAV column

Please could someone assist me, any suggestions will be much appricated 

[v-juanli-msft]

Hi @TH123

Are "Opening & Closing NAV." columns or measure?

Are the "January to September" values in the column "month" which is added to the "column" field of the matrix?

Best Regards

Maggie

[TH123]

Hi Maggie 

The Opening & Closing NAV's are columns - these are uploaded into power bi directly from my data source being excel

The same applies to the information for the months, ie January to September, these are also uploaded directly from my data source 

Taryn 

[v-juanli-msft]

Hi @TH123

If your dataset is like this

name	open	1	2	3	4	5	6	7	8	9	close
a	1	2	3	4	5	6	7	8	9	10	100
b	2	3	4	5	6	7	8	9	10	11	130
c	3	1	2	3	4	5	6	7	8	9	110

you could use simple formula in power bi as below

create columns:

Flows YTD = [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]

market movement column = [close]-[Flows YTD]-[open]

If i misunderstand your problem, please let me know.

Best regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TH123]

HI Maggie

apologies for the delayed reply

Thank you so much for your help!

Your suggestion may be able to work but my data is not setup that way - please see snippet below

do you have any other suggestions please?