Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
omarevp
Helper II
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:

Capture.JPG

 

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  (This should be Resultado Operacional - Resultado No Operacional )  250  < The last result
     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>

 

https://drive.google.com/drive/folders/15GbeJRhq9yd49ituJh-_QB2UrzQeLOu3?usp=sharing

 

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

1 ACCEPTED SOLUTION
MFelix
Super User
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"
        )
    )


Resultado No Operacional =
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.

Multi-level sum.png

 

Hope this helps.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
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"
        )
    )


Resultado No Operacional =
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.

Multi-level sum.png

 

Hope this helps.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



FiveAces
Frequent Visitor
FiveAces
Frequent Visitor
v-piga-msft
Resident Rockstar
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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.