Forum Discussion
Need help with a DAX Measure to complete a Income Statement dashboard
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 a...
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
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.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))