cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## How to show different measures in a matrix table in Power BI?

Good afternoon to all,

I have the necessity to show in a single column of a matrix different measures.

I found some tips in the forum so I followed these steps:

1) Calculated all the measures;

2) Created a table with two columns; first: Id, second: the descriptions of the row i want to show;

3) Created a measure to expose each measure calculated in step1 into the matrix. The measure is this:

Importo geo settoriale =
SUMX(TPAR_RCGS_Settori_Geo,
IF([Settore] = "Imprese finanziarie ed assicurative", [EAD_IMPRESE],
""))))))))
I get this matrix:

Each row has the correct value calculated in point 1 and even the grand total is correct.

Now I come to the point.

I applied some multiplication in each measure to make a what if analysis.

If I check the value of each measure it is correct while if i put it into the matrix (with the same method) it doesn't return me the right value.

I give you an example:

I would like that the value shown in the firs row could be 7.383.084,67€ while it returns me another value. Even the grand totale is wrong

Probably there is some issue with the filter context.

Can you help me?

Thank you

Enrico

1 ACCEPTED SOLUTION
Memorable Member

@ermeggy_1298  @v-linyulu-msft  in my opinion you have to give the granularity of the sector in your measures, like:

in this way the measure keeps the context of the sector, giving the correct value in row and in the total.

BBF
6 REPLIES 6
Community Support

Thanks for the reply from @BeaBF , please allow me to provide another insight:

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Since the output contains multiple judgment statements, I recommend using the SWITCH() function. Here are my calculation columns:

``````Importo geo settoriale =
SWITCH(TRUE(),
[Settore] = "Imprese finanziarie ed assicurative", [EAD_IMPRESE],
BLANK())
``````

In My calculation columns, you can modify the output to add, subtract, multiply, divide, and so on.

3.Here's my final result, which I hope meets your requirements.

SWITCH function (DAX) - DAX | Microsoft Learn

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Good afternoon,

I tried with the solution you gave but it is not working.

https://we.tl/t-KAmIjPEB7C

Please consider the sheet "Hp2_rischio di conc.geo-settoriale stress".

I tried to write three measues with 3 different methods:

First one:
Importo geo settoriale stress =
SUMX(TPAR_RCGS_Settori_Geo,
IF([Settore] = "Agricoltura", [EAD AGRI STRESS_2],
IF([Settore] = "Amministrazioni pubbliche", [EAD AMMI STRESS_2],
IF([Settore] = "Edilizia", [EAD EDIL STRESS_2],
IF([Settore] = "Famiglie consumatrici", [EAD FAMI STRESS_2],
IF([Settore] = "Imprese finanziarie ed assicurative", [EAD IMPR STRESS_2],
IF([Settore] = "Industria", [EAD INDU STRESS_2],
IF([Settore] = "Servizi", [EAD SERV STRESS_2],
""))))))))
Second one:
Importo geo settoriale stress 2 =
SWITCH(true(),
TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 1, [EAD AGRI STRESS_2],
TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 2, [EAD AMMI STRESS_2],
TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 3, [EAD EDIL STRESS_2],
TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 4, [EAD FAMI STRESS_2],
TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 5, [EAD IMPR STRESS_2],
TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 6, [EAD INDU STRESS_2],
TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 7, [EAD SERV STRESS_2],
blank())

Third one:
Importo geosettoriale stress_3 = IF(HASONEVALUE(TPAR_RCGS_Settori_Geo[Settore]),
SWITCH(SELECTEDVALUE(TPAR_RCGS_Settori_Geo[Settore]),
"Imprese finanziarie ed assicurative", [EAD IMPR STRESS_2],
)

As you can see in the matrix each one gives me the same values.
I expect, for example that in the row "Agricoltura" the value will be 7.383.084,67€ while it gives me 8.366.111,53€.
It seems the something in the filter context deactivate the calculations that are made to obtain the calculated measure.

Best regards

Enrico
Memorable Member

@ermeggy_1298  @v-linyulu-msft  in my opinion you have to give the granularity of the sector in your measures, like:

in this way the measure keeps the context of the sector, giving the correct value in row and in the total.

BBF
New Member

Thanks to your advice I found the solution. The problem was that the "layout" table in which there are the sectors names is connected to the data table so the filter context was activated.

I created a new layout teble which is not relate to the data table and now all the calculated measures are reported correctly in the calculated column.

Enrico

New Member

I tried even this measure but it doesn't work.

Importo geo settoriale stress 4 =
VAR EADAGRISTRESS1 = IF([Selezione stress geo settoriale] = 1, [EAD_AGRICOLTURA] * (1+[Valore % Incremento EAD GS Stress]),0)
VAR EADAMMISTRESS1 = IF([Selezione stress geo settoriale] = 2, [EAD_AMM.PUBBLICHE] * (1+[Valore % Incremento EAD GS Stress]),0)
VAR EADEDILSTRESS1 = IF([Selezione stress geo settoriale] = 3, [EAD_EDILIZIA] * (1+[Valore % Incremento EAD GS Stress]),0)
VAR EADFAMISTRESS1 = IF([Selezione stress geo settoriale] = 4, [EAD_FAMIGLIE] * (1+[Valore % Incremento EAD GS Stress]),0)
VAR EADIMPRSTRESS1 = IF([Selezione stress geo settoriale] = 5, [EAD_IMPRESE] * (1+[Valore % Incremento EAD GS Stress]),0)
VAR EADINDUSTRESS1 = IF([Selezione stress geo settoriale] = 6, [EAD_INDUSTRIA] * (1+[Valore % Incremento EAD GS Stress]),0)
VAR EADSERVSTRESS1 = IF([Selezione stress geo settoriale] = 7, [EAD_SERVIZI] * (1+[Valore % Incremento EAD GS Stress]),0)
RETURN
SWITCH(true(),
blank())
Memorable Member

@ermeggy_1298  can you share the pbix? or paste sample data and your current measures.

BBF

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors