Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ermeggy_1298
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] = "Agricoltura", [EAD_AGRICOLTURA],
                      IF([Settore] = "Amministrazioni pubbliche", [EAD_AMM.PUBBLICHE],
                      IF([Settore] = "Edilizia", [EAD_EDILIZIA],
                      IF([Settore] = "Famiglie consumatrici", [EAD_FAMIGLIE],
                      IF([Settore] = "Imprese finanziarie ed assicurative", [EAD_IMPRESE],
                      IF([Settore] = "Industria", [EAD_INDUSTRIA],
                      IF([Settore] = "Servizi", [EAD_SERVIZI],
                      ""))))))))
I get this matrix:
ermeggy_1298_0-1717426521077.png

 

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:

ermeggy_1298_3-1717426898069.png

 

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

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

EAD AGRI STRESS_2 = SUMX(VALUES(TPAR_RCGS_Settori_Geo[Settore]), [EAD RESIDUA] * [RIP AGRI_STRESS])
 
in this way the measure keeps the context of the sector, giving the correct value in row and in the total.
 
BBF

View solution in original post

6 REPLIES 6
v-linyulu-msft
Community Support
Community Support

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

Hi,@ermeggy_1298 

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:

vlinyulumsft_0-1717490729726.png

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] = "Agricoltura" ,[EAD_AGRICOLTURA]*[EAD_INDUSTRIA],
                      [Settore] = "Amministrazioni pubbliche", [EAD_AMM.PUBBLICHE],
                      [Settore] = "Edilizia", [EAD_EDILIZIA],
                      [Settore] = "Famiglie consumatrici", [EAD_FAMIGLIE],
                      [Settore] = "Imprese finanziarie ed assicurative", [EAD_IMPRESE],
                      [Settore] = "Industria", [EAD_INDUSTRIA],
                      [Settore] = "Servizi", [EAD_SERVIZI],
                      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.

vlinyulumsft_1-1717490800320.png

 

4.The following are links to related documents, hope to help you:

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.

Good afternoon, 

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

I provide you a link where you can download the file I am working with:

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]),
                                    "Agricoltura", [EAD AGRI STRESS_2],
                                    "Amministrazioni pubbliche", [EAD AMMI STRESS_2],
                                    "Edilizia", [EAD EDIL STRESS_2],
                                    "Famiglie consumatrici", [EAD FAMI STRESS_2],
                                    "Imprese finanziarie ed assicurative", [EAD IMPR STRESS_2],
                                    "Industria", [EAD INDU STRESS_2],
                                    "Servizi", [EAD SERV 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

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

EAD AGRI STRESS_2 = SUMX(VALUES(TPAR_RCGS_Settori_Geo[Settore]), [EAD RESIDUA] * [RIP AGRI_STRESS])
 
in this way the measure keeps the context of the sector, giving the correct value in row and in the total.
 
BBF

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

ermeggy_1298_0-1717509368073.png

 

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

Importo geo settoriale stress 4 =
VAR EADPARAMETRO =
                IF([Selezione stress geo settoriale] = 1, [EAD_TOTALE] - [EAD_AGRICOLTURA],
                IF([Selezione stress geo settoriale] = 2, [EAD_TOTALE] - [EAD_AMM.PUBBLICHE],
                IF([Selezione stress geo settoriale] = 3, [EAD_TOTALE] - [EAD_EDILIZIA],
                IF([Selezione stress geo settoriale] = 4, [EAD_TOTALE] - [EAD_FAMIGLIE],
                IF([Selezione stress geo settoriale] = 5, [EAD_TOTALE] - [EAD_IMPRESE],
                IF([Selezione stress geo settoriale] = 6, [EAD_TOTALE] - [EAD_INDUSTRIA],
                IF([Selezione stress geo settoriale] = 7, [EAD_TOTALE] - [EAD_SERVIZI])))))))
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)
VAR EADRESIDUA = [EAD_TOTALE] - EADAGRISTRESS1 - EADAMMISTRESS1 - EADEDILSTRESS1 - EADFAMISTRESS1 - EADIMPRSTRESS1 - EADINDUSTRESS1 - EADSERVSTRESS1 - EADSERVSTRESS1
VAR RIPAGRISTRESS = IF ([Selezione stress geo settoriale]<>1, DIVIDE([EAD_AGRICOLTURA], EADPARAMETRO),0)
VAR RIPAMMISTRESS = IF ([Selezione stress geo settoriale]<>2, DIVIDE([EAD_AMM.PUBBLICHE], EADPARAMETRO),0)
VAR RIPEDILSTRESS = IF ([Selezione stress geo settoriale]<>3, DIVIDE([EAD_EDILIZIA], EADPARAMETRO),0)
VAR RIPFAMISTRESS = IF ([Selezione stress geo settoriale]<>4, DIVIDE([EAD_FAMIGLIE], EADPARAMETRO),0)
VAR RIPIMPRSTRESS = IF ([Selezione stress geo settoriale]<>5, DIVIDE([EAD_IMPRESE], EADPARAMETRO),0)
VAR RIPINDUSTRESS = IF ([Selezione stress geo settoriale]<>6, DIVIDE([EAD_INDUSTRIA], EADPARAMETRO),0)
VAR RIPSERVSTRESS = IF ([Selezione stress geo settoriale]<>7, DIVIDE([EAD_SERVIZI], EADPARAMETRO),0)
VAR EADAGRISTRESS2 = IF([Selezione stress geo settoriale] = 1, EADAGRISTRESS1, EADRESIDUA * RIPAGRISTRESS)
VAR EADAMMISTRESS2 = IF([Selezione stress geo settoriale] = 2, EADAMMISTRESS1, EADRESIDUA * RIPAMMISTRESS)
VAR EADEDILSTRESS2 = IF([Selezione stress geo settoriale] = 3, EADEDILSTRESS1, EADRESIDUA * RIPEDILSTRESS)
VAR EADFAMISTRESS2 = IF([Selezione stress geo settoriale] = 4, EADFAMISTRESS1, EADRESIDUA * RIPFAMISTRESS)
VAR EADIMPRSTRESS2 = IF([Selezione stress geo settoriale] = 5, EADIMPRSTRESS1, EADRESIDUA * RIPIMPRSTRESS)
VAR EADINDUSTRESS2 = IF([Selezione stress geo settoriale] = 6, EADINDUSTRESS1, EADRESIDUA * RIPINDUSTRESS)
VAR EADSERVSTRESS2 = IF([Selezione stress geo settoriale] = 7, EADSERVSTRESS1, EADRESIDUA * RIPSERVSTRESS)
RETURN
    SWITCH(true(),
      TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 1, EADAGRISTRESS2,
      TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 2, EADAMMISTRESS2,
      TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 3, EADEDILSTRESS2,
      TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 4, EADFAMISTRESS2,
      TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 5, EADIMPRSTRESS2,
      TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 6, EADINDUSTRESS2,
      TPAR_RCGS_Settori_Geo[Selezione ID_RCGS] = 7, EADSERVSTRESS2,
      blank())
BeaBF
Memorable Member
Memorable Member

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

 

BBF

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors