Need help with a DAX Measure to complete a Income Statement dashboard

[jnsrkd]

Hello all,

I'm "attaching" the PBIX file and the Excel file with data (mock values) below:

Excel: Link

PBIX: Link

Although in the example above the values come from an Excel sheet, I'm actually gathering all the data from a database using SQL.

I need help with a DAX measure:

I'm trying to create a DRE (Income Statement) from an ERP system called Totvs Protheus. Most of it is complete; however, it needs a final sum of the aggregation accounts:

ACCOUNT	STATUS	VALUE ORIGIN
1.1 RECEITA	✔	ACCOUNTING ENTRY
2.1 DEDUCOES	✔	ACCOUNTING ENTRY
3.1 RECEITA LIQ	✘	1.1 - 2.1
4.1 CUSTOS	✔	ACCOUNTING ENTRY
5.1 LUCRO OP	✘	3.1 - 4.1
6.1 DESPESAS	✔	ACCOUNTING ENTRY
7.1 LUCRO	✘	5.1 - 6.1
8.1. RECEITAS	✔	ACCOUNTING ENTRY
9.1 LUCRO FIN	✘	7.1 - 8.1
10.1 RESULTADO N OP	✔	ACCOUNTING ENTRY
11.1 RESULTADO	✘	10.1 - 11.1
12.1 IRPJ E CSLL	✔	ACCOUNTING ENTRY
13.1 LUCRO	✘	11.1 - 12.1

The table above shows what I already have figured it out (✔), and what is missing (✘)

Thefore, what I have is:

The accounts jump from 1.1 and 2.1 to 4.1, the 3.1 doesn't appear.

And what I must achieve is:

In this example, some of the dimension table data came from Excel, which made it possible to set the group accounts. Also note that:

3 RECEITA OPERACIONAL LIQUIDA  = 1. VENDA DE PRODUTOS - 2 DEDUCOES E ABATIMENTOS

My company doesn't want to use any Excel files in the dashboard; therefore, all information must come from the data I provided (which comes from the Database/ERP).

The logic to sum the missing parts is in the table "Visao Formula" I provided, with the following relationship:

'Visao Formula'[Formula] - 'Visao Sintetica'[Entid_Gerenc]

And the aggregation accounts (3.1, 5.1, 7.1, 9.1, 11.1, 13.1) must be in between the remaining accounts.

If someone could please help, I think I'm already a month stuck with this Dashboard with no idea how to actually complete.

Thank you very much!

[FBergamaschi]

If you can provide a pbix I can fix it

[CaioSouza123]

Bom dia! Tudo bem?

Eu vi seu post e estou tentando fazer integração do DataSul com o Power BI, gostaria de saber se foi isso que você fez. Caso sim, poderia me ajudar?

[SachinNandanwar]

[jnsrkd]

From what I can gather, the gif is explaining that the relationships don't allow the values from Dados to appear?

I'm sorry, I don't think I understood what you are trying to say

[SachinNandanwar]

Yes thats correct, because  in your original question you mentioned that: "The accounts jump from 1.1 and 2.1 to 4.1, the 3.1 doesn't appear."

So the GIF was just trying to show why it isnt appearing.

[jnsrkd]

Ah, yes, I see. Indeed, just using relationships it won't work. I completed a course on Udemy that explains how to set up a income statement dashboard using totvs protheus on power bi. However, the problem is, the professor uses a excel spreadsheet to set up the dimension tables, and I must use the DRE database. In the course, there is a complex DAX measure to achieve all the sums, and I think there needs to be something similar the solve my problem.

VAR GERAL = SUM(F_DRE_REALIZADO[VLR_REAL])
VAR RECEITA_LIQUIDA = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),PRI_NIV[COD_GRP_FOR] IN {1,4}))
VAR LUCRO_BRUTO_MARGEM = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),PRI_NIV[COD_GRP_FOR] IN {1,4,8}))
VAR MARGEM_PERCENTUAL = DIVIDE(LUCRO_BRUTO_MARGEM,RECEITA_LIQUIDA)*100
VAR EBITDA = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),[COD_GRP_FOR] IN {1,4,8,15,24,25}))
VAR EBITDA_PERCENTUAL = DIVIDE(EBITDA, RECEITA_LIQUIDA)*100
VAR LUCRO_OPERACIONAL = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),[COD_GRP_FOR] IN {1,4,8,15,24,25,28}))
VAR LUCRO_OPERACIONAL_ANTES_IR = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),[COD_GRP_FOR] IN {1,4,8,15,24,25,28,30,34}))
VAR LUCRO_LIQUIDO = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),[COD_GRP_FOR] IN {1,4,8,15,24,25,28,30,34,37,38}))
VAR FLUXO_CAIXA_BRUTO = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),[COD_GRP_FOR] IN {1,4,8,15,24,25,28,30,34,37,38,40,44}))
VAR FLUXO_CAIXA_LIVRE = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),[COD_GRP_FOR] IN {1,4,8,15,24,25,28,30,34,37,38,40,44,46}))
VAR FLUXO_CAIXA_ACIONISTA = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),[COD_GRP_FOR] IN {1,4,8,15,24,25,28,30,34,37,38,40,44,46,51,52}))
VAR SALDO_FINAL_CAIXA = CALCULATE(SUM(F_DRE_REALIZADO[VLR_REAL]),FILTER(ALL(PRI_NIV),[COD_GRP_FOR] IN {1,4,8,15,24,25,28,30,34,37,38,40,44,46,51,52,54}))

RETURN

SWITCH(TRUE(),

ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 7, RECEITA_LIQUIDA,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 13, LUCRO_BRUTO_MARGEM,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 14,MARGEM_PERCENTUAL,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 26,EBITDA,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 27,EBITDA_PERCENTUAL,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 29,LUCRO_OPERACIONAL,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 35,LUCRO_OPERACIONAL_ANTES_IR,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 39,LUCRO_LIQUIDO,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 45,FLUXO_CAIXA_BRUTO,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 50,FLUXO_CAIXA_LIVRE,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 53,FLUXO_CAIXA_LIVRE,
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE() && MAX(PRI_NIV[COD_GRP_FOR]) = 55,FLUXO_CAIXA_LIVRE, 
ISINSCOPE(PRI_NIV[DESC_GRP_FOR]) = TRUE() && ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = FALSE(),IF(GERAL = BLANK(),0,GERAL),
ISINSCOPE(SEG_NVL[SEG_NVL_FORM]) = TRUE() && MAX(SEG_NVL[SEG_NVL_FORM]) <> BLANK(),IF(GERAL = BLANK(),0,GERAL))