topic Re: DAX - I need help to upgrade my measure in DAX Commands and Tips

DAX - I need help to upgrade my measure

jnsrkd — Tue, 03 Sep 2024 13:12:37 GMT

Hello,

I'm currently working on upgrading my measure and have hit a bit of a roadblock. Here’s where I’m at:

Current Setup

I have a working solution, but I need to add some additional conditions.

Analitico_Valor_Realiz = VAR MinContaIni = MIN('Visao'[Conta_Ini]) VAR MaxContaFinal = MAX('Visao'[Conta_Final]) VAR MinCCustoIni = MIN('Visao'[CCusto_Ini]) VAR MaxCCustoFinal = MAX('Visao'[CCusto_Final]) RETURN CALCULATE( SUM('Dados'[VALOR_REALIZADO]), 'Dados'[CODIGO_CONTA] >= MinContaIni && 'Dados'[CODIGO_CONTA] <= MaxContaFinal && 'Dados'[CENTRO_CUSTO] >= MinCCustoIni && 'Dados'[CENTRO_CUSTO] <= MaxCCustoFinal )+0

What I Need:

I need to incorporate additional conditions into the measure. I’m attaching a PBIX file that currently works, as well as an Excel file that contains both the base data and the expected result, which I achieve through formulas.

= SUMIF ( 'Visao'[Entid_Super]; 'Visao'[Entid_Gerenc]; 'Dados'[VALOR(REALIZADO] )

Context:

The 'Visao' dimension table comes from an ERP system (Totvs Protheus). I’m attempting to create a DRE (Income Statement). Both the 'Visao' dimension table and the necessary data are fed into the PBIX file through SQL.

However, I also need to include additional conditions. I'm attaching a PBIX file which currently works, and a Excel file which contains both the base data and the expected result, which I achieve thorugh formulas.

PBIX File (Google Drive)

Excel File (Google Drive)

Current result:

What i want to achieve:

Could someone assist me with this, or provide some guidance on possible solutions?

Thank you very much!

Re: DAX - I need help to upgrade my measure

mark_endicott — Tue, 03 Sep 2024 13:48:18 GMT

jnsrkd - You don't need DAX for this.

You can use a Matrix Visual and use the column that defines "ANALITICA" and "SINTETICA" alongside the "Desc_Ent_Ger2" column in the rows section. You then just need to play around with the visual formatting to acheiv the result you want.

In the screenshot below, I have expanded both categories and set the layout style to "Tabular"

If this works, please accept as the solution for others visibility.

Re: DAX - I need help to upgrade my measure

jnsrkd — Tue, 03 Sep 2024 14:03:07 GMT

I'm sorry; in hindsight, I wasn't clear about the image formatting.

The problem lies in the values.

I basically want to create an accounting report of the company's results (monthly).

Currently, the accounts (which are in the dimension table 'Visao') sum only the accounting recordings throughout the month, and these are "analitica" accounts.

However, there are grouped accounts that sum the "analytical" accounts. These are "sintetica" accounts.

So, while I have already solved summing the accounting records using the DAX measure, I now need to also group the sums.

I apologize for not creating the PBIX file and Excel file with the same formatting.

In the example (internet snippet, no correlation with my files) below, the green check marks are what I already have solved, and the orange x's are what I'm trying to achieve.

Re: DAX - I need help to upgrade my measure

jnsrkd — Tue, 03 Sep 2024 17:47:37 GMT

Instead of creating a measure, I created a relationship between the tables.

While power bi doesn't accept range relationships:

('Dados'[Contas] Between 'Visao'[Conta_Ini] and 'Visao'[Conta_Final]

I expanded the possible accounts of 'Visao'[Conta_Final] and 'Visao'[Conta_Ini] using List.Text.

I also duplicated the 'Visao' and filted 'Analitica' and 'Sintetica', and created a relationship between them too.