Divide two columns DAX

[volkanbygl]

Hello,

I need to calculate percentage and this is the DAX formula which isnt working:

PercentDifference= DIVIDE('Query1'[SALES]*100; 'Query1'[PLANNED_SALES] )

Divide function works if I put like this: 

PercentDifference= DIVIDE('Query1'[SALES]*100; 2 )

But when I put column PLANNED_SALES instead of number two... as a result I get empty column... Why is that? thank you!

[volkanbygl · Accepted solution]

Hello all, 

Thank you for you efforts. I finally found the solution. 

What I needed to do was:

- create three measures:

    Sale = SUM(Query1[SALES])*100

    PlannedSale = SUM(Query1[PLANNED_SALES])

    Percentage= DIVIDE([Sale];[PlannedSale])

Only this way worked for me and it is cool. Thank you guys 

[NotMyJob]

This syntax did not work for me:    Percentage= DIVIDE([Sale];[PlannedSale])

Instead of an " ;  " I had to use a comma.  I should have read the example....🤣    But for those going quickly with copy and paste, RTFM!   I had a headache for about 10 minutes before I caught this.

[Paulomongo]

Hi,

I am fairly new to this, so please bear with me.

I have two values from my dataset, both numerics. Drop and Connect, I need to show the Drop as a % of Drop and Connect, so created the below measure.

M_DropRate = 
VAR Drops = sum(Query2[Drops])
VAR DropsConnects = (sum(Query2[Connects]) + sum(Query2[Drops]))

RETURN DIVIDE(Drops,DropsConnects)

When I add the measure to my table, it returns zero, but there are values in DROP and CONNECT. Any ideas??

[ozandikerler]

Power BI gives error when I add a new column and type = IFERROR( ([Hedeflenen]-[Gerceklesen])/[Hedeflenen],0)

"Expression.Error: The name 'IFERROR' wasn't recognized.  Make sure it's spelled correctly."

[newpi]

Hi,

I'm trying to do a simple division of two numerci field columns but when I write the DAX measure. The columns don't load by itself. Its showing me colored text but there is no dropdown appearing as I type the column name. Both the numeric fields are part of the same table. 

[Phil_Seamark]

What are some of the values you have in rows where you are getting blank?

Can you please post a small screenshot showing the two columns being passed to the DIVIDE function, and the result.

[volkanbygl]

When I put formula like this:

PercentDifference= DIVIDE('Query1'[SALES]*100; 2 )

There is no error, and the column PercentDifference returns result just fine.

But, I need to put in the formula like this:

PercentDifference= DIVIDE('Query1'[SALES]*100; Query1[PLANNED_SALE] )

 In this case, the column PercentDifference is EMPTY. Like in the image:

http://prntscr.com/i59yvd

Why, when I put second parameter (planned_sale) the result is nothing? But if I put number 2, it is fine

[volkanbygl]

Right now I am testing:

I cannot even do this:

PERCENT_DIFFERENCE = DIVIDE(Query1[SALES]; Query1[PLANNED_SALE] )

Result is also EMPTY column, not zero, nor anything, just empty. Does it mean that DIVIDE function cannot divide two columns? 

[Phil_Seamark]

HI @volkanbygl

What happens if you try a different operator.

PERCENT_DIFFERENCE_test = Query1[SALES] +  Query1[PLANNED_SALE] 

or even this

PERCENT_DIFFERENCE_test2 = Query1[SALES]

or

PERCENT_DIFFERENCE_test4 =  Query1[PLANNED_SALE] 

 finally

PERCENT_DIFFERENCE_test = Query1[SALES] / Query1[PLANNED_SALE]

Are Query1[Sales] and Query1[Planned_sale] both physical columns, and not calculated columns or measures?

What does the column show in the Data View?  Rather than using a visual

[volkanbygl]

Hello, 

thank you for being there. 

What happens if you try a different operator.

PERCENT_DIFFERENCE_test = Query1[SALES] +  Query1[PLANNED_SALE] 

with plus operator, the result is good.

or even this

PERCENT_DIFFERENCE_test2 = Query1[SALES]

with assigning it 'copies' just fine

or

PERCENT_DIFFERENCE_test4 =  Query1[PLANNED_SALE] 

 finally

PERCENT_DIFFERENCE_test = Query1[SALES] / Query1[PLANNED_SALE]

BUT, with / operator I get infinity in every row in a column

When I put * operator, the column is empty

what could it be?

[Phil_Seamark]

Sorry to keep suggesting tests but how about this one

PERCENT_DIFFERENCE_test5 = IFERROR( Query1[SALES] / Query1[PLANNED_SALE] , -1)