LOD? DAX?

[Anonymous]

Is there a way to get the value of On Hand to be 32 using a calculated field??  My goal is to get the correct value which is 32 without using Average for the component 12P9813X032. But the value is showing 1280 because it's adding all the rows as showing in the table below. 

Is there a way to get 32 using calculated field?

[selimovd · Accepted solution]

Hey @Anonymous ,

I think I found the reason. You added the formula as calculated column, but you have to add it as measure:

Then the result is correct:

To understand the difference, a calculated column is just a new column in the table. In your case you added a new column that returned for every row the average of

tableforqty[ONHAND_QTY] what is for each row the same value as the column tableforqty[ONHAND_QTY]. When you put that calculated column in the matrix the visual was summing up the averages, what was equal to the total.

If you use a measure it will always be calculated on the fly and in the context that you are using it. So if you put the measure in the matrix, it will calculate the average for each Component_Code and also for the total.

You can see the difference also by the logo. The calculated column [On Hand Average] has the icon of a table, the measure [On Hand Average as Measure] has the icon of a measure.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd thank you so much for this wonderful information. i didnot know it until you mentioned it to me. thanks!

[Tahreem24]

@Anonymous  I don't quite well understand your scenario. But What I understood from your post is you want 32 as an output for on hand average.

On Hand Average  Measure = DIVIDE(SUM(TableName[RequiredQuantity]),SUM(TableName[On Hand Sum]))

[Anonymous]

@Tahreem24 

I have a column in the table "Global_Inventory.On_Hand"  

i need to have a calculated field that will show it in the view to be 32, but it's showing 1280 because it's adding all the rows.