Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Divide calculation is wrong in DAX and Power Query

I have simple data below

qty and shares

if i divide Qty/Shares in calculator below are results

but in Power BI i am getting 0

i need to display 4.2 in my visualization.

 

i literally tried many ways, unable to get a result. kindly 

 

amitchandak Greg_Deckler mwegener 

 

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    You can try this

     

    First 3 = LEFT(FORMAT(SciNum[Num], "Scientific"), 3)

     

    Hope this helps

    David

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    You may try my way as well.

    Column = 
var _a=DIVIDE('Table'[Qty],'Table'[Share])
return 
MID(_a,1,3)
    Column 2 = 
VAR _a =
    DIVIDE ( 'Table'[Qty], 'Table'[Share] )
RETURN
    LEFT ( _a * POWER ( 10, LEN ( _a ) - 2 ), 2 ) / 10

    Result:

    You can download the pbix file from this link: Divide calculation is wrong in DAX and Power Query

     

    Best Regards,

    Rico Zhou

     

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

6 Replies

    • dedelman_clng's avatar
      dedelman_clng
      Community Champion
      5 years ago

      Anonymous - also realize that 4.2e-6 is 0.0000042, which at 2 decimal places is 0.00.  You would need to multiply by 1,000,000 for the actual value to be 4.2

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        dedelman_clng  is there a way to extract only first 2 digits from the sceintific number? like 4.2 only

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    You may try my way as well.

    Column = 
var _a=DIVIDE('Table'[Qty],'Table'[Share])
return 
MID(_a,1,3)
    Column 2 = 
VAR _a =
    DIVIDE ( 'Table'[Qty], 'Table'[Share] )
RETURN
    LEFT ( _a * POWER ( 10, LEN ( _a ) - 2 ), 2 ) / 10

    Result:

    You can download the pbix file from this link: Divide calculation is wrong in DAX and Power Query

     

    Best Regards,

    Rico Zhou

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou