cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nannimora Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User II
Super User II

Re: Measure that don't considering zero/blank

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)

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Measure that don't considering zero/blank

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 Regular Visitor
Regular Visitor

Re: Measure that don't considering zero/blank

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.

Super User II
Super User II

Re: Measure that don't considering zero/blank

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)

View solution in original post

Highlighted
nannimora Regular Visitor
Regular Visitor

Re: Measure that don't considering zero/blank

hi @tex628 ,

 

perfect, thank you.

 

Smiley Happy

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors