Division error - fields does not appear

[Anonymous]

Hi,

I have error with division in dax formula.

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

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

the correct one is Integrated gross profit/external sales.

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

[V-pazhen-msft]

@Anonymous 
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 · Accepted solution]

@Anonymous 
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.

[Anonymous]

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

[Anonymous]

Thank you @V-pazhen-msft 

[Anonymous]

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]

Hi @Anonymous ,

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