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

Division error - fields does not appear

Hi,

 

I have error with division in dax formula.

 

When using new measure : There is no numerator or denominator link to the fields. 

Sharene_0-1632903418116.png

 

When using quick measure, I could divide but is dont want to sum the value.

Sharene_1-1632903549708.png

 

the correct one is Integrated gross profit/external sales.

 

Anyone has idea why this error occur? Thanks in advance. 

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@Sharene 
You need to add an function to refer to a value when creating a measure. In your case, using sum, max, min or any other function does not change the result. And to get the current year margin%, you can create a measure with filter to today()'s year:

 

Current Year Margin% = Calculate( [Margin%], Filter(Table, [Date].[Year]= Year(Today()))

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
V-pazhen-msft
Community Support
Community Support

@Sharene 
What is your formula looks like? Or you can use following instead.

 

Last Year Margin% = Calculate( [Margin%], Filter(Table, [Date].[Year]= Year(Today())-1))

 

 

Paul

V-pazhen-msft
Community Support
Community Support

@Sharene 
You need to add an function to refer to a value when creating a measure. In your case, using sum, max, min or any other function does not change the result. And to get the current year margin%, you can create a measure with filter to today()'s year:

 

Current Year Margin% = Calculate( [Margin%], Filter(Table, [Date].[Year]= Year(Today()))

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @V-pazhen-msft ,

 

I have tried using your formula for current year margin and it works. However, I tried to compute last year margin with sameperiodlastyear formula, the calculation i got is wrong. Do you know why?

 

Regards,

Sharene

Thank you @V-pazhen-msft 

Sharene
Frequent Visitor

Hi Pete,

 

Thanks for the reply. Yes the formula is correct however I am calculating margin % for different years. When i compute a line graph, my margin for both years has been combine which I wanted to split into different years for comparison.

 

I am able to margin % for LY with sameperiodlastyear formula but this year margin is the total margin for both years. How do I get marign for this year?

BA_Pete
Super User
Super User

Hi @Sharene ,

 

This doesn't look like an error.

The quick-measure GUI is summing your values as you're using columns/fields as the inputs.

Columns/fields contain many values, but you can only divide a single value by another single value, so Power BI is aggregating (summing here, but could be average, min, max etc) all the values in the column to one value.

 

What you are asking PBI to do:

1, 5, 6, 8 / 3, 7, 6

What PBI is aggregating this to:

20 / 16

 

Similarly, in your first picture where you are typing the DIVIDE function manually, you will need to aggregate your column values in order for DAX to accept them as numerator/denominator values, something like this:

DIVIDE(
  SUM(yourTable[Integratedgross profit]),
  SUM(yourTable[External sales]),
  0
)

 

Hope this makes sense.

 

Pete

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors