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
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)
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.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |