Simple fields are showing in Data model as aggregation filed

[shabirAhmad]

Hi Experts,

we are using dynamics ERP system and in that a table inventsum table as i am selecting data from using SQL Server as datasource the simple fields are showing in aggregate format as shon below

I want these should be show as simple fields such as in above image DataAreaID field is showing as a simple field any idea thanks in advance.

Best Regards,

Shabir Ahmad

[some_bih · Accepted solution]

Hi @shabirAhmad it could be that your columns are mainly number data types. During transformation in PowerQuery you can change it and apply wanted format.

[Idrissshatila]

Hello @shabirAhmad ,

this is a sign that they are a numeric value and can be aggregated. f you want them to show without this sign you then convert the data type to text.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[shabirAhmad]

Thanks for both of you for valauble replies, actually, i want to find out "On Hand" Quantity of item so i wrote the below DAX in measure but not working is it write?

On Hand Qty = SUM(POWERBIUEINVENTONHANDITEM[POSTEDQTY]) + SUM(POWERBIUEINVENTONHANDITEM[RECEIVED]) - SUM(POWERBIUEINVENTONHANDITEM[DEDUCTED] )+ SUM(POWERBIUEINVENTONHANDITEM[REGISTERED]) - SUM(POWERBIUEINVENTONHANDITEM[PICKED])

  View from SQL Server    Name is    POWERBIUEINVENTONHANDITEM and remaining are fields  [fields]

Best Regards,

Shabir Ahmad

[some_bih]

Hi @shabirAhmad if you are using data from SQL maybe you should check link https://learn.microsoft.com/en-us/power-query/query-folding-examples 

[shabirAhmad]

@some_bih ,

Thanks for your reply, i am using views from Dynamics ERP , and these views are shown as views in SQL SERVER No check the below item 00000010 quanity on hand 289

The same item in Power BI show 610 that's strange

Best Regards,

Shabir Ahmad

[Idrissshatila]

hello @shabirAhmad ,

can yoiu check it in power query to see if its readung the data correctly and from where is the issue coming from.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[shabirAhmad]

@Idrissshatila ,

Thanks for your reply and support please check here it showing all transaction data even showing the figure which i need 289 check the below image of power Query. If we plus these values it gives 610 which is in coming in power bi report

Best Regards,

Shabir Ahmad

[Idrissshatila]

@shabirAhmad ,

so the sum is true, but you may be missing a filter or a column that would differentiate the row row you want from others.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[shabirAhmad]

Hi @Idrissshatila ,

What kind of filter i should apply to get this value or i should give more item number and quantities of these item so that you can understand it well.

Best Regards,

Shabir Ahmad

[Idrissshatila]

@shabirAhmad ,

depends on your data, so how each record in power query is differentiated, is it by date or customer or a place, so it depends on what is the diff between all these rows because they all have item id in common so what's not in common between them?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[shabirAhmad]

@Idrissshatila 

These items are differentiated by Dates.

Best Regards,

Shabir Ahmad 

[Idrissshatila]

@shabirAhmad ,

exactly so if you add to the table the date you'll get the number you want.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[shabirAhmad]

@Idrissshatila ,

Sorry, but i didn't get your last reply, what will be the solution for this. Let say if i want last date total quantity do you have any idea or DAX formula so that i can apply and get my on hand quantity?

Best Regards,

Shabir Ahmad

[Idrissshatila]

@shabirAhmad ,

so try to add a filter date and try to select the last date to see if its giving the result you want.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[shabirAhmad]

@Idrissshatila ,

Can you please check the below image it is a simple field with  dates, how can i get and show in p ower BI Report why it showing wrong qty below image.

Best Regards,

Shabir Ahmad

[Idrissshatila]

@shabirAhmad ,

there should be a sort of field here that makes each row unique like an id or something so that they don't aggregate, so what it is? use it in power bi so it displays as you want.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[shabirAhmad]

@Idrissshatila ,

But it shows all the data and old transaction but my required value is also there for item 00000010

Qty onhand 289 shown below:-

Best Regards,

Shabir Ahmad

[Idrissshatila]

@shabirAhmad ,

so you need to remove the old history and keep the new ones that you need.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[some_bih · Accepted solution]

Hi @shabirAhmad it could be that your columns are mainly number data types. During transformation in PowerQuery you can change it and apply wanted format.

[shabirAhmad]

Hi @some_bih ,

Thanks for your reply.  i did as you suggest me but as i am trying to add those field in dax formula it giving an error of not accepting this type of data or string of data for total.Because i am doing addition and substration of these fields to get "On Hand" Quantity of Items.

Best Regards,

Shabir Ahmad

[Idrissshatila · Accepted solution]

Hello @shabirAhmad ,

in order to use them as sum or subtract they should be with a numeric type and should show the aggregate sign which is normal.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡