Forum Discussion

nannimora's avatar
nannimora
Helper I
7 years ago
Solved

calculation error on powerbi

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))
        }
numerocodice_articolovaloreZZ. Fatturato Netto Sconti1. Fatturatosconto_1sconto_2sconto_3sconto_4sconto_piede_1sconto_piede_2sconto_pagamento
2611XXXXX€ 1.005,48€ 955,21€ 9550000005
2605XXXXX€ 712,80€ 712,80€ 7130000000
2598XXXXX€ 134,02€ 120,95€ 1215000005
2597XXXXX€ 2.359,80€ 2.241,81€ 2.24210000000

 

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
Need Help
  • dax's avatar
    dax
    7 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

     

     

     

  • dax's avatar
    dax
    Community Support
    7 years ago

    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.