Measure that don't considering zero/blank

[nannimora]

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:

now i link you a table:

Numero_Doc	Data_Fattura	Data_Pagamento	Data_Scad_Orig	Importo_Totale	Importo_Incassato	ZZ. Residuo	ZZ. cTMI	ZZ. cRMI	1. TMI	1. RMI
44	05/07/2019	01/01/1900	31/12/2019	€               966,90	€                             -	€ 966,90	-	-	0	0
45	08/08/2018	01/01/1900	31/01/2019	€             5.541,80	€                             -	€ 5.541,80	-	-	0	0
46	23/08/2018	01/01/1900	31/01/2019	€             5.813,72	€                             -	€ 5.813,72	-	-	0	0
47	31/08/2018	01/01/1900	31/01/2019	€             2.367,75	€                             -	€ 2.367,75	-	-	0	0
48	20/09/2018	01/01/1900	28/02/2019	€             4.583,00	€                             -	€ 4.583,00	-	-	0	0
49	12/10/2018	01/01/1900	31/03/2019	€             1.186,68	€                             -	€ 1.186,68	-	-	0	0
50	31/10/2018	01/01/1900	31/03/2019	€             3.108,49	€                             -	€ 3.108,49	-	-	0	0
51	05/07/2017	16/04/2018	31/12/2017	€           22.000,00	€                 22.000,00	€ 0,00	6.270.000,00	2.332.000,00	285	106
52	23/06/2017	16/05/2018	30/11/2017	€             6.589,00	€                   6.589,00	€ 0,00	2.154.603,00	1.100.363,00	327	167
53	05/07/2017	16/05/2018	31/12/2017	€             8.768,04	€                   8.768,04	€ 0,00	2.761.932,60	1.192.453,44	315	136
54	11/07/2017	16/05/2018	31/12/2017	€             6.839,04	€                   6.839,04	€ 0,00	2.113.263,36	930.109,44	309	136
55	13/07/2017	16/05/2018	31/12/2017	€             1.238,27	€                   1.238,27	€ 0,00	380.148,89	168.404,72	307	136
56	14/02/2018	25/10/2018	31/05/2018	€           13.260,00	€                 13.260,00	€ 0,00	3.354.780,00	1.949.220,00	253	147
57	23/05/2018	21/01/2019	31/10/2018	€           15.926,33	€                 15.926,33	€ 0,00	3.870.098,19	1.305.959,06	243	82
58	04/07/2018	28/02/2019	31/10/2018	€             6.825,00	€                   6.825,00	€ 0,00	1.631.175,00	819.000,00	239	120
59	25/07/2018	28/02/2019	31/10/2018	€             9.750,00	€                   9.750,00	€ 0,00	2.125.500,00	1.170.000,00	218	120
60	22/03/2018	29/03/2019	31/08/2018	€             1.563,85	€                   1.563,85	€ 0,00	581.752,20	328.408,50	372	210
61	04/05/2018	29/03/2019	31/08/2018	€             6.825,00	€                   6.825,00	€ 0,00	2.245.425,00	1.433.250,00	329	210
62	29/05/2018	29/03/2019	31/08/2018	€             8.775,00	€                   8.775,00	€ 0,00	2.667.600,00	1.842.750,00	304	210
63	04/05/2018	18/04/2019	31/10/2018	€           15.000,00	€                 15.000,00	€ 0,00	5.235.000,00	2.535.000,00	349	169
64	29/05/2018	22/05/2019	31/10/2018	€           12.487,30	€                 12.487,30	€ 0,00	4.470.453,40	2.534.921,90	358	203
65	31/05/2018	22/05/2019	31/10/2018	€             2.905,76	€                   2.905,76	€ 0,00	1.034.450,56	589.869,28	356	203
66	22/01/2019	30/06/2019	30/06/2019	€             3.419,52	€                   3.419,52	€ 0,00	543.703,68	-	159	0
67	04/05/2018	26/07/2019	31/10/2018	€             5.130,00	€                             -	€ 5.130,00	-	-	0	0
68	11/05/2018	26/07/2019	31/10/2018	€             1.763,85	€                             -	€ 1.763,85	-	-	0	0
69	14/06/2018	26/07/2019	30/11/2018	€             8.717,28	€                             -	€ 8.717,28	-	-	0	0
70	11/05/2018	29/08/2019	31/10/2018	€           15.000,00	€                             -	€ 15.000,00	-	-	0	0

[TeigeGao]

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

[nannimora]

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 · Accepted solution]

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)

[nannimora]

hi @tex628 ,

perfect, thank you.