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?

[Anonymous]

When adding Hands on column to your table visual right click and you will see many operations such as SUM, Average etc etc. Use average. If it does'nt work please explain a bit more so that I can fully understand what you are doing.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

the on hand column is adding all the rows that is why it's showing 1280, is there a way to show 32  for the on hand sum column?

[Anonymous]

 Can you access this for the On_hand Column ??? there select Average from this dropdown.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

hi @Anonymous 

Can i use it for another calculated field?

like  how do i do,

ON_HAND - REQUIRED.QUANTITY 

should be 32 - 40 , desired outout = -8

if on_hand correct value is just from the dropdown but not came from the calculated column

[Anonymous]

You can create a calculated column as shown below

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

@Anonymous 

the data looks like this actually,

my goal is to get the output below:

REQUIRED QUANTITY = ADDING ALL THE ROWS

ON_HAND = calculated field that will only get the minimum/average

shortage = ON_HAND - REQUIRED QUANTITY (TOTAL OF 40)

[Anonymous]

You'll show the final output in table visual?

you can change the calculated column to this 

Desired Output = SUM(Sheet1[Required])-Sheet1[On Hand]

[Anonymous]

@Anonymous still not working. you might want to check the sample file i created.

https://www.dropbox.com/s/bo84cwqa7lpmcha/samplepbi.pbix?dl=0

[selimovd]

Hey @Anonymous ,

yes, you can exclude that item from the calculation.

Try the following approach:

Average without 12P9813X032 =
CALCULATE(
    AVERAGE( myTable[ValueColumn] ),
    myTable[COMPONENT_CODE] <> '12P9813X032'
)

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]

hi @selimovd it's showing blank as an output using the calculation you gave

[selimovd]

Sorry, then I didn't get what you want to do. What exactly do you want to archive?

[Anonymous]

@selimovd 

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 1241 because it's adding all the rows. 

[selimovd]

Hey @Anonymous ,

and what is the formula of the measure [On Hand Average]?

The following should give you the average of 32:

On Hand Average = AVERAGE( myTable[ValueColumn] )

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 here is the file that you can use

https://www.dropbox.com/s/2k5e9mbmzsh0cek/TEST.pbix?dl=0

[selimovd]

Hey @Anonymous ,

that's the formula for the measure:

On Hand Average = AVERAGE( Data[ONHAND_QTY] )

And then it will return the average:

The file you gave me had 35 as value for Component_Code 12P9813X032 and not 32 as shown in your first screenshot.

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]

hello @selimovd why is it different with when the pbix is connecting to actual data source which is from dataflow.

[selimovd]

@Anonymous  When the result is different, then the underlying data will be different. Take a look at the table and analyze it. You can also share the file with me, then I can take a look.

[Anonymous]

hi @selimovd how do i share file with confidential data? I really want to but there is so much confidentality. and it's connecting to dataflow. how is that possible for sharing?

[selimovd]

Hey @Anonymous ,

no if there is confidential data you can't share. You can import the data from dataflow, then you could share the file.

In this case take a look at the data table and check the values in the column. The average for Component_Code 12P9813X032 will be 112'429.53, so there should be some big values.

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 i was able to hide some of the confidential columns. you may now check the sample pbix, to help me fix the solution.

https://www.dropbox.com/s/bo84cwqa7lpmcha/samplepbi.pbix?dl=0