cancel
Showing results for
Did you mean:  Helper II

## Accounting results matrix / by several levels

Hello guys, I hope you all are well.

This time I'm trying to do a matrix table, which have a list of categories in several levels, with month columns. It is a result table for accounting issues.

Now, I already did all the levels in my matrix, and this is what I got so far: I need a master measure that calculates Amount by every level, but the most difficult is that I need it to be like this:

Resultado Operacional (This should be Margen - Total G.A.V) 350
Margen (this should be Total Ventas - Total Costos de Ventas)  650
Total Ventas                                  1000

Total Costos de Ventas                   350

Total G.A.V. (this should be a SUM of all the accounts inside it)  300

Asesoria y Servicios Externos          100

Transporte                                       100

Servicios Basicos                               50

Patentes                                      50

Resultado No Operacional (This should be a SUM of all the accounts inside it)  100

No Operacional ( this should be a SUM of all the accounts inside it) 100
Ingreso no Operacional                      50

Gastos Financieros                              20

Impuestos                                           30

Im sending here a google drive link with both archives (.pbix and the Excel file), in case you need it>

Guys, I know you can help me!!! Please, and THANKS A LOT!

1 ACCEPTED SOLUTION  Super User

Hi @omarevp ,

Believe that the best way is to create the singular measures for each of your levels and then make it the calculations on a measure to put on the matrix.

```Margen =
CALCULATE (
SUM ( 'Datos Origen'[Monto Neto] );
FILTER (
ALL ( 'Cuentas y Conceptos'[Padre] );
'Cuentas y Conceptos'[Padre] = "Total Ventas"
)
)
- CALCULATE (
SUM ( 'Datos Origen'[Monto Neto] );
FILTER (
ALL ( 'Cuentas y Conceptos'[Padre] );
'Cuentas y Conceptos'[Padre] = "Total Costos de Ventas"
)
)

CALCULATE (
SUM ( 'Datos Origen'[Monto Neto] );
FILTER (
ALL ( 'Cuentas y Conceptos'[Totales] );
'Cuentas y Conceptos'[Totales] = "Resultado No Operacional"
)
)

Total G.A.V =
CALCULATE (
SUM ( 'Datos Origen'[Monto Neto] );
FILTER (
ALL ( 'Cuentas y Conceptos'[Subtotales] );
'Cuentas y Conceptos'[Subtotales] = "Total G.A.V."
)
)

Neto =
SWITCH (
TRUE ();
SELECTEDVALUE ( 'Cuentas y Conceptos'[Subtotales] ) = "Margen"
&& NOT ( HASONEFILTER ( 'Cuentas y Conceptos'[Padre] ) ); [Margen];
SELECTEDVALUE ( 'Cuentas y Conceptos'[Totales] ) = "Resultado Operacional"
&& NOT ( HASONEFILTER ( 'Cuentas y Conceptos'[Subtotales] ) ); [Margen] - [Total G.A.V];
NOT ( HASONEFILTER ( 'Cuentas y Conceptos'[Totales] ) ); [Margen] - [Total G.A.V] - [Resultado No Operacional];
SUM ( 'Datos Origen'[Monto Neto] )
)```

Final result below and in attach PBIX file. Hope this helps.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português 4 REPLIES 4  Super User

Hi @omarevp ,

Believe that the best way is to create the singular measures for each of your levels and then make it the calculations on a measure to put on the matrix.

```Margen =
CALCULATE (
SUM ( 'Datos Origen'[Monto Neto] );
FILTER (
ALL ( 'Cuentas y Conceptos'[Padre] );
'Cuentas y Conceptos'[Padre] = "Total Ventas"
)
)
- CALCULATE (
SUM ( 'Datos Origen'[Monto Neto] );
FILTER (
ALL ( 'Cuentas y Conceptos'[Padre] );
'Cuentas y Conceptos'[Padre] = "Total Costos de Ventas"
)
)

CALCULATE (
SUM ( 'Datos Origen'[Monto Neto] );
FILTER (
ALL ( 'Cuentas y Conceptos'[Totales] );
'Cuentas y Conceptos'[Totales] = "Resultado No Operacional"
)
)

Total G.A.V =
CALCULATE (
SUM ( 'Datos Origen'[Monto Neto] );
FILTER (
ALL ( 'Cuentas y Conceptos'[Subtotales] );
'Cuentas y Conceptos'[Subtotales] = "Total G.A.V."
)
)

Neto =
SWITCH (
TRUE ();
SELECTEDVALUE ( 'Cuentas y Conceptos'[Subtotales] ) = "Margen"
&& NOT ( HASONEFILTER ( 'Cuentas y Conceptos'[Padre] ) ); [Margen];
SELECTEDVALUE ( 'Cuentas y Conceptos'[Totales] ) = "Resultado Operacional"
&& NOT ( HASONEFILTER ( 'Cuentas y Conceptos'[Subtotales] ) ); [Margen] - [Total G.A.V];
NOT ( HASONEFILTER ( 'Cuentas y Conceptos'[Totales] ) ); [Margen] - [Total G.A.V] - [Resultado No Operacional];
SUM ( 'Datos Origen'[Monto Neto] )
)```

Final result below and in attach PBIX file. Hope this helps.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português  Frequent Visitor Frequent Visitor  Resident Rockstar

Hi @omarevp ,

By my research based on your sample data, I'm afraid that there is no default option for us to achieve your desired output in matrix currently.

Hope @Greg_Deckler and @MFelix  have other ideas.

Best  Regards,

Cherry

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