cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
volkanbygl Frequent Visitor
Frequent Visitor

Divide two columns DAX

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!

1 ACCEPTED SOLUTION

Accepted Solutions
volkanbygl Frequent Visitor
Frequent Visitor

Re: Divide two columns DAX

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 

9 REPLIES 9
Phil_Seamark Super Contributor
Super Contributor

Re: Divide two columns DAX

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

volkanbygl Frequent Visitor
Frequent Visitor

Re: Divide two columns DAX

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 Frequent Visitor
Frequent Visitor

Re: Divide two columns DAX

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 Super Contributor
Super Contributor

Re: Divide two columns DAX

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

volkanbygl Frequent Visitor
Frequent Visitor

Re: Divide two columns DAX

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 Super Contributor
Super Contributor

Re: Divide two columns DAX

Sorry to keep suggesting tests but how about this one

 

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

volkanbygl Frequent Visitor
Frequent Visitor

Re: Divide two columns DAX

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 

Paulomongo Visitor
Visitor

Re: Divide two columns DAX

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??drop.JPG

ozandikerler Frequent Visitor
Frequent Visitor

Re: Divide two columns DAX

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."

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 108 members 1,589 guests
Please welcome our newest community members: