cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.