cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nannimora
Helper I
Helper I

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
1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi nannimore,

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

123.png

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.

View solution in original post

2 REPLIES 2
dax
Community Support
Community Support

Hi nannimore,

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

123.png

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
Community Champion
Community Champion

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

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.