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 |
Solved! Go to 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)
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.
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)
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
User | Count |
---|---|
221 | |
46 | |
45 | |
44 | |
42 |
User | Count |
---|---|
277 | |
211 | |
82 | |
75 | |
63 |