Sum of matrix columns.

[Anonymous]

down vote
favorite
I have to create the following MatrixPreview:

Month      january                                   february
Desc            Value       sum_Value               Value       sum_Value
DescA          V_A           V_A + V_B + V_C       V_X       V_X + V_Y + V_Z
DescB          V_B           V_A + V_B + V_C       V_Y        V_X + V_Y + V_Z
DesC           V_C           V_A + V_B + V_C       V_Z        V_X + V_Y + V_Z

This is my data relationship:

These are my fields:

And this is my table:

I have to create a new measure or column that contains a DAX formula to sum all the values by month. How can I that using my data?

[dedelman_clng · Accepted solution]

Basically you want to remove the "filter" on Descgrupo that the matrix forms implicitly, while keeping the "filter" for Mes.  You will use the ALL() function on the column to remove that filter.  The filter on Mes will remain.

FirstSM = Calculate( sum(VRTOTAL), ALL('Groupo De Despesa'[Descgrupo]) )

Hope this helps

David

[TH123]

HI

In my matrix below there are 2 things I need to do

1. How do I sum only the months ( I.e January to September) doing this will provide me with my Flows YTD and this formula should not include the opening nav and closing nav. This information should be displayed between the September column & Closing NAV column

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

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

Please could someone assist me

[dedelman_clng]

Hi @TH123 - I would suggest you create a new post for your question, as this one is marked as solved. Include sample data and expected outcomes and you're likely to get more assistance.

[Anonymous]

@dedelman_clng, thank you, it worked! But I did not understand your explanation, can you explain me again?

[dedelman_clng]

DAX uses the concept of "filter context" to define the "conditions" under which a value is calculated.  When you create a matrix or table view in PowerBI, the filter context becomes the rows and columns that you have in your visual.

The CALCULATE function allow you to do "context transition" which replaces and/or augments the current filter context with whatever conditions you specify after the expression to be calculated.  I come from a SQL background, so I often think of CALCULATE giving you the ability to specify a "WHERE" clause, but in DAX.

In your case, you want the value to only look at month and not at category.  So by using CALCULATE and doing a context transition to ALL categories (instead of the one specified by the row), you get DAX to give you a different value.

Microsoft offers some basic training on DAX at the end of the curriculum here: Power BI Self-Guided Learning.  They also have other resources here: DAX Resource Center

Hope this makes sense.

David

[Anonymous]

Thanks for your solution, suggestions and assistence!