calculation error on powerbi

[nannimora]

Goodevening, 

good evening,working on a power bi report, I noticed that there are calculation errors on a formula. or rather, sometimes the error is revealed other times not.

the formula is:

ZZ. Fatturato Netto Sconti = Y_documento_ven[valore]*
    {
        (1-(Y_documento_ven[sconto_1]/100))*
        (1-(Y_documento_ven[sconto_2]/100))*
        (1-(Y_documento_ven[sconto_3]/100))*
        (1-(Y_documento_ven[sconto_4]/100))*
        (1-(Y_documento_ven[sconto_piede_1]/100))*
        (1-(Y_documento_ven[sconto_piede_2]/100))*
        (1-(Y_documento_ven[sconto_pagamento]/100))
        }

numero	codice_articolo	valore	ZZ. Fatturato Netto Sconti	1. Fatturato	sconto_1	sconto_2	sconto_3	sconto_4	sconto_piede_1	sconto_piede_2	sconto_pagamento
2611	XXXXX	€ 1.005,48	€ 955,21	€ 955	0	0	0	0	0	0	5
2605	XXXXX	€ 712,80	€ 712,80	€ 713	0	0	0	0	0	0	0
2598	XXXXX	€ 134,02	€ 120,95	€ 121	5	0	0	0	0	0	5
2597	XXXXX	€ 2.359,80	€ 2.241,81	€ 2.242	10	0	0	0	0	0	0

as you can see, in the first line applies the discount correctly, in the case of the last line does not apply the 10% discount but makes a mistake.why?

€ 1.005,48 * (1-(5/100)) =955,21 

€ 2359,80 * (1-(10/100)) = 2123,82 instead of 2241,81 (seems to apply the 5% discount instead of 10%)

Thank you

[dax · Accepted solution]

Hi nannimore,

Yes, based on your sample  data, I create a calculated column, this will get the result  like below

So I am not sure how you get result 224181, you could try to recreate this column to see whether it works or not. Or check whether caused by your sample data(check whether this is 10 instead of 5)

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CheenuSing]

Hi @nannimora ,

With your data provided I got the results both as calculated column as well as measure.

Please check

1. valore is of type Fixed Decimal with 2 places.  Check the values are properly placed with decimal point at the correct palces.  ( 1.005,48 should be 1005.48   etc)

2. The Measure is slighlty modified

1.Fatturato = SUMX(Y_documento_ven,(Y_documento_ven[valore]*		
    (		
        (1-(Y_documento_ven[sconto_1]/100))*		
        (1-(Y_documento_ven[sconto_2]/100))*		
        (1-(Y_documento_ven[sconto_3]/100))*		
        (1-(Y_documento_ven[sconto_4]/100))*		
        (1-(Y_documento_ven[sconto_piede_1]/100))*		
        (1-(Y_documento_ven[sconto_piede_2]/100))*		
        (1-(Y_documento_ven[sconto_pagamento]/100))		
    )		
))

Using the query editor you can also add a custom column  ZZ. Fatturato Netto Sconti

= Table.AddColumn(#"Removed Columns", "ZZ. Fatturato Netto Sconti", each [valore]*
    (
        (1-([sconto_1]/100))*
        (1-([sconto_2]/100))*
        (1-([sconto_3]/100))*
        (1-([sconto_4]/100))*
        (1-([sconto_piede_1]/100))*
        (1-([sconto_piede_2]/100))*
        (1-([sconto_pagamento]/100))
        ) )

Cheers

CheenuSing