Help with DAX Meassure

Question

Hello everyone,

I'm diving into DAX measures and, being my second try at DAX, I have an issue with the meassures that I don't understand, or DAX, really, for that matter.

Sample file: https://1drv.ms/x/s!AmrjlXSYqMxegZo7priwmzmmcLgfrA?e=axmzpO

Disclosure: data is publicly available information accesible from the Central Bank's website to internet users from all over the world. Original data is in Spanish and it's quite a bit to translate by myself. hope this won't be an issue. The data in the file is a sample, the actual data set is much, much bigger.

Issue:
I wish to create a data model that will allow me to quickly analize my country's insurance market. But I have 3 different markets, that need to be calculated and is why I wish to create a DAX measure for each.

How I got here:
a) After importing the data and transforming it through power query, I have the resulting table "Resultado_Técnico", viewble in the file at the "Resultado Técnico" sheet. This is the facts table. (For the sample file I've broken the original connection to avoid posible errors and simply left a sample table)
b) Created 3 dimension tables:

1. "tbl_cia", viewable at the "Tabla Compañías" sheet, represents the companies that operate in the market. Note: the company "CONSOLIDADO" or "MERCADO" is
   the market itself.
2. "tbl_cuentas", viewable at the "Tabla Cuentas" sheet, represents the accounts into wich the financial information is organized. Note: the account "Primas emitidas
   netas de anulacs." is the gross written premiums, how the output/production is meassured in the insurance industry.
3. "tbl_ramas", viewable at the "Tabla Ramas" sheet, represents the lines of business the market is segmented into. Note: the line of business "Total" is
   the market itself.
   c) Uploaded the facts table and the dimension tables to the data model.
   d) Added a 4th dimension table, Calender, renamed "tbl_fechas".
   e) Created the DAX measures, all located in the "Resultado_Técnico", or facts table, of the data model.
4. "Mercado total", this represents the total market through the sum of written premiums, the account name is "Primas emitidas netas de anulacs."
5. "Mercado abierto", this represents the open market through the sum of written premiums, the account name is "Primas emitidas netas de anulacs.", of some
   "ramas", lines business that segment the market, that are available to all privately owned companies. Some segments are only exploited by the state owned
   company, "BSE", for reasons that are not to the poinf of the issue.
6. "Mercado en competencia", this represents the market segments in which the company I work at operates, through the sum of written premiums, the account name
   is "Primas emitidas netas de anulacs.".
   f) A ranking measure for the different markets explained at e).

Open market excludes the following lines of business:
-Accidentes
-Invalidez
-Reaseguros Activos
-Seguro Colectivo
-Seguro de Renta Vitalicia
-Vida Prev.

Market in which my company operates excludes the following lines of business:
-Accidentes
-Invalidez
-Reaseguros Activos
-Seguro Colectivo
-Seguro de Renta Vitalicia
-Vida Prev.
-Accidente
-Caución
-Crédito
-Ingeniería
-Invalidez
-Robo
-Rurales
-Salud
-Vida Colectivos (RM)
-Vida individual (NRM)
-Vida individual (RM)

Problem:
In the sheet "Mercado por cia 2020", Market by companies in 2020, the measures work as expected and in a single pivot table, with the companies in the rows and lines of busniess in the columns, to filter for "Total" (I tried applying this filter at the measure, but then the pivot table would only show the grand total at every row), and the 3 measures in the values. The measure "Rank" works as well.
However, in the sheet "Mercado por rama 2020", Market by line of business 2020, the measures don't work as expected. The idea of the pivot table in this sheet is to have the lines of business in the rows and the companies in the column, to filter for "MERCADO", the market itself, and the the 3 measures in the values.
Now, "Mercado total", total market, does work, but the other two, "Mercado abierto", open market, and "Mercado en competencia", the market in which my company operates, show negative values at every row that I dont understand, but the pivot table's Grand Total shows the correct value.

In the same sheet I've added three pivot tables created only from the facts table that show the expected results for each meassure. For reporting purposes, I wish to have this in a single pivot table.

Is there a way to make this work for the pivot table at the sheet "Mercado por rama 2020" like it works for the pivot table at the sheet "Mercado por cia 2020"?

I'm at my wits end, with my limited experience.

Thanks in advance,

Shinka

Answer 1

Hi,

To be honest, the question is too long to comprehend. Could you simplify the question, and focus on the core requirement , so we could help better.

Answer 2

Hi @Lukas Yu -MSFT ,

I wanted to bring as much detail as posible. Perhaps it was too much. My apologies.

I want to create 3 DAX meassures that display the total Market, open Market and a segmented version of the Market, so as to see all versions of the market in a single pivot table.

I've managed to do so when the Pivot Table is organized by companies in the rows, but when trying to see it by the products available in the Market in the rows, the Pivot Table shows negative values at each row although the grand total is ok.

Is it posible to modify the DAX measure to contemplate this need? Is it posible to create new DAX meassures that would contemplate this need?

Thanks,

Shinka

Answer 3

@Shinka
From a logical point of view, these two measures, "Mercado abierto" and "Mercado en competencia" cannot be applied to "Market by line of business 2020".
Let us choose one measure, "Mercado abierto", to have a check.

On the following image, you dag the field "Rama(Hijo)" into Rows part, it means column values need to meet the conditions of each line of business.

The following formula is for "Mercado abierto" measure.

The values of [Mercado total] in your pivot table means the total sum of "Monto" for each line of business (each Rama (Hijo)), such as the total sum of "Monto" for "Accidentes" Rama (Hijo).

But the part of CALCULATE(SUM('Resultado_Técnico'[Monto]),tbl_cuentas[Sub-cuenta]="Primas emitidas netas de anulacs.",tbl_ramas[Rama (Hijo)]="Accidentes"||tbl_ramas[Rama (Hijo)]="Invalidez"||tbl_ramas[Rama (Hijo)]="Reaseguros Activos"||tbl_ramas[Rama (Hijo)]="Seguro Colectivo"||tbl_ramas[Rama (Hijo)]="Seguro de Renta Vitalicia"||tbl_ramas[Rama (Hijo)]="Vida Prev.") , it means excepting for this condition of "Primas emitidas netas de anulacs." Sub-cuenta, if the Rama (Hijo) is "Accidentes"or "Invalidez" or "Reaseguros Activos" or "Seguro Colectivo" or "Seguro de Renta Vitalicia" or "Vida Prev.", then values of "Monto" would be all calculated via this formula.
So the result of this part formula is bigger than the [Mercado total] value for each row in PivotTable.

As we are not the expert of insurance market, we are not very familiar with the inner connection of each field on your table. If you want to analyse the values for each line of business, you need to find new formula according to your needs.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Emily Hua-MSFT

Thanks for your replay. I see what you mean. I'm going to try another approach, but I'm starting to think it just might not be posible to do what I'm hoping.

I'll post updates if I get anywhere productive.

Cheers!