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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
nannimora
Helper I
Helper I

Measure that don't considering zero/blank

Hi everyone,

I need your help to solve my problem.

I created two columns:

ZZ. cTMI = IF(SYS_CREDEB_PARTITAC[Data_Fattura]<DATE(2000;01;01);0;SYS_CREDEB_PARTITAC[Importo_Incassato]*IF(SYS_CREDEB_PARTITAC[Data_Pagamento]=DATE(1900;01;01);0;SYS_CREDEB_PARTITAC[Data_Pagamento]-SYS_CREDEB_PARTITAC[Data_Fattura]))

ZZ. cRMI = IF(SYS_CREDEB_PARTITAC[Data_Fattura]<DATE(2000;01;01);0;SYS_CREDEB_PARTITAC[Importo_Incassato]*IF(SYS_CREDEB_PARTITAC[Data_Pagamento]=DATE(1900;01;01);0;SYS_CREDEB_PARTITAC[Data_Pagamento]-SYS_CREDEB_PARTITAC[Data_Scad_Orig]))

and two measures:

 

1. TMI = IFERROR(SUM(SYS_CREDEB_PARTITAC[ZZ. cTMI])/SUM(SYS_CREDEB_PARTITAC[Importo_Incassato]);0)

1. RMI = IFERROR(SUM(SYS_CREDEB_PARTITAC[ZZ. cRMI])/SUM(SYS_CREDEB_PARTITAC[Importo_Incassato]);0)

 

here an exlaple:

Annotazione 2019-07-11 185814.jpg

 

now i link you a table:

 

Numero_DocData_FatturaData_PagamentoData_Scad_OrigImporto_TotaleImporto_IncassatoZZ. ResiduoZZ. cTMIZZ. cRMI1. TMI1. RMI
4405/07/201901/01/190031/12/2019€               966,90€                             -  € 966,90                     -                       -  00
4508/08/201801/01/190031/01/2019€             5.541,80€                             -  € 5.541,80                     -                       -  00
4623/08/201801/01/190031/01/2019€             5.813,72€                             -  € 5.813,72                     -                       -  00
4731/08/201801/01/190031/01/2019€             2.367,75€                             -  € 2.367,75                     -                       -  00
4820/09/201801/01/190028/02/2019€             4.583,00€                             -  € 4.583,00                     -                       -  00
4912/10/201801/01/190031/03/2019€             1.186,68€                             -  € 1.186,68                     -                       -  00
5031/10/201801/01/190031/03/2019€             3.108,49€                             -  € 3.108,49                     -                       -  00
5105/07/201716/04/201831/12/2017€           22.000,00€                 22.000,00€ 0,006.270.000,002.332.000,00285106
5223/06/201716/05/201830/11/2017€             6.589,00€                   6.589,00€ 0,002.154.603,001.100.363,00327167
5305/07/201716/05/201831/12/2017€             8.768,04€                   8.768,04€ 0,002.761.932,601.192.453,44315136
5411/07/201716/05/201831/12/2017€             6.839,04€                   6.839,04€ 0,002.113.263,36     930.109,44309136
5513/07/201716/05/201831/12/2017€             1.238,27€                   1.238,27€ 0,00     380.148,89     168.404,72307136
5614/02/201825/10/201831/05/2018€           13.260,00€                 13.260,00€ 0,003.354.780,001.949.220,00253147
5723/05/201821/01/201931/10/2018€           15.926,33€                 15.926,33€ 0,003.870.098,191.305.959,0624382
5804/07/201828/02/201931/10/2018€             6.825,00€                   6.825,00€ 0,001.631.175,00     819.000,00239120
5925/07/201828/02/201931/10/2018€             9.750,00€                   9.750,00€ 0,002.125.500,001.170.000,00218120
6022/03/201829/03/201931/08/2018€             1.563,85€                   1.563,85€ 0,00     581.752,20     328.408,50372210
6104/05/201829/03/201931/08/2018€             6.825,00€                   6.825,00€ 0,002.245.425,001.433.250,00329210
6229/05/201829/03/201931/08/2018€             8.775,00€                   8.775,00€ 0,002.667.600,001.842.750,00304210
6304/05/201818/04/201931/10/2018€           15.000,00€                 15.000,00€ 0,005.235.000,002.535.000,00349169
6429/05/201822/05/201931/10/2018€           12.487,30€                 12.487,30€ 0,004.470.453,402.534.921,90358203
6531/05/201822/05/201931/10/2018€             2.905,76€                   2.905,76€ 0,001.034.450,56     589.869,28356203
6622/01/201930/06/201930/06/2019€             3.419,52€                   3.419,52€ 0,00     543.703,68                     -  1590
6704/05/201826/07/201931/10/2018€             5.130,00€                             -  € 5.130,00                     -                       -  00
6811/05/201826/07/201931/10/2018€             1.763,85€                             -  € 1.763,85                     -                       -  00
6914/06/201826/07/201930/11/2018€             8.717,28€                             -  € 8.717,28                     -                       -  00
7011/05/201829/08/201931/10/2018€           15.000,00€                             -  € 15.000,00                     -                       -  00
1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

You need to apply the following filter to both you measures:

TMI = IFERROR(CALCULATE(SUM(SYS_CREDEB_PARTITAC[ZZ. cTMI])/SUM(SYS_CREDEB_PARTITAC[Importo_Incassato]) ,
SYS_CREDEB_PARTITAC[ZZ. cTMI] <> 0 ,
SYS_CREDEB_PARTITAC[Importo_Incassato] <> 0
);0) RMI = IFERROR(CALCULATE(SUM(SYS_CREDEB_PARTITAC[ZZ. cRMI])/SUM(SYS_CREDEB_PARTITAC[Importo_Incassato]) ,
SYS_CREDEB_PARTITAC[ZZ. cRMI] <> 0 ,
SYS_CREDEB_PARTITAC[Importo_Incassato] <> 0
);0)

Connect on LinkedIn

View solution in original post

4 REPLIES 4
TeigeGao
Solution Sage
Solution Sage

Hi @nannimora ,

It's sorry that I can't understand your requirement well, what's problem did you meet, how do you want to handle the zero and blank?

Best Regards,

Teige

Hi @TeigeGao ,

 

I would like that when i calculate the measure TMI, it doesn't count the zero and the blank.

I'll try to explain myself better. 

For example:

if u look the image, the total of the measure 1. TMI is 291,48. beacouse it count also the zero. 

If i filter the table without 0, the total of 1.TMI change.

I want the total of TMI without 0 or blanks. and I don't know how I have to fix my measure.

 

I hope now you understand my problem.

And sorry for my english.

 

Regards.

tex628
Community Champion
Community Champion

You need to apply the following filter to both you measures:

TMI = IFERROR(CALCULATE(SUM(SYS_CREDEB_PARTITAC[ZZ. cTMI])/SUM(SYS_CREDEB_PARTITAC[Importo_Incassato]) ,
SYS_CREDEB_PARTITAC[ZZ. cTMI] <> 0 ,
SYS_CREDEB_PARTITAC[Importo_Incassato] <> 0
);0) RMI = IFERROR(CALCULATE(SUM(SYS_CREDEB_PARTITAC[ZZ. cRMI])/SUM(SYS_CREDEB_PARTITAC[Importo_Incassato]) ,
SYS_CREDEB_PARTITAC[ZZ. cRMI] <> 0 ,
SYS_CREDEB_PARTITAC[Importo_Incassato] <> 0
);0)

Connect on LinkedIn

hi @tex628 ,

 

perfect, thank you.

 

Smiley Happy

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.