Division DAX does not give the correct result

[Avivek]

I have created a measure for ASP which is 

ASP= (DIVIDE(Sales[Sales Amount],(SUM(Sales[Item Qty])))

However this shows wrong values for some of the accounts. So I tried to modify the measure as

ASP = (DIVIDE(Sales[Sales Amount],(SUM(Sales[Item Qty]))),ALLEXCEPT(Account,Account[ACCOUNT ID]))

But this measure does not work at all.

THe measure should be Sales/Item Quantity for every Account ID
Can someone please guide me where the measure is failing and what might be the solution.

[Fowmy · Accepted solution]

@Avivek 

Create a measure for Qty as

Qty =SUM(Sales[Item Qty]) 

Your division measure will be:

ASP= DIVIDE( [Sales Amount]  , [Qty] )

If the above doesn't work m you will have to share a sample PBIX file to check what exactly is happening in your file.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn  

[Avivek]

@Fowmy , the measure works for the overall total for an account but the individual records shows wrong values.

In the above image, Qty and ASP is the measures you had suggested and Measure2 is the old measure I had created. Both the measures give similar result but if you see the 1st record then with the measure of Qty it shows 6 instead of 3, similarly the 3rd record for Qty measure shows 4 instead of 2. I believe that is why the measure is calculating as (2712/6) whereas it should be (2712/3). 

Do you know what is the change in the measure I can do to get the appropriate result.

[Ashish_Mathur]

Hi,

The formulas suggested by Fowmy have to be written as measures (not calculated column formulas).

[v-yingjl]

Hi @Avivek ,

Basically the formula as @ Fowmy mentioned should work. Did the [Item Qty] column is a calculated column or a normal column in your table? If it is a calculated column, how did you calculate it?

Could you please consdier sharing a sample file for further discussion?

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Avivek 

Is "Item Qty" a measure?

Provide clear details about your model and the measures you have created.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Avivek]

Oh sorry @Fowmy , actually the Sales amount is a measure and Item Quantity is a column.

[Fowmy]

@Avivek 

ASP=

DIVIDE([Sales Amount]),CALCULATE(SUM(Sales[Item Qty])))

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Avivek]

It still shows wrong value

Can you please tell if ther is anything else we can do

[Fowmy]

@Avivek 

Try the following measure:

ASP=

DIVIDE(SUM(Sales[Sales Amount]),SUM(Sales[Item Qty]))

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Avivek]

Thankyou @Fowmy , but sales amount is a measure so i cannot use sum(sales amount)