Measures Calculating Incorrectly at Line Level

[Anonymous]

Hello,

Hoping someone can help me.

I'm trying to do something very simple and basic in Power BI, yet each attempt using different methods is coming up incorrecct as well.

I have inventory items with a current quantity per each.  If I bring in the current quantity field and do not sum, it breaks it down to 4 lines with different amounts, however, when you add each line item, you get the correct sum.  Yet when you sum the the Quantity, it gives you a very incorrect result.  

My first screenshot shows the item, broken out into multiple lines as I've indicated, with a different quantity on each line.  The total of those is 130.

Now, if I sum the quantity to get one line item for each item, this is what happens:

The sum is clearly incorrect.  Some of the items are so badly incorrect (you can see the high numbers below the indicated line).

I have been searching for days trying to find a solution, yet each attempt I make is showing the data incorrectly.  Summarizing doesn't work, neither does group by; variables aren't working..............nothing helps.

Please help if you can!

Thank you!

Michelle

[Anonymous · Accepted solution]

@AlexisOlson I was finally successful and was able to create the proper sum per item using this measure:

Current Qty = SUMX(SUMMARIZE(vw_TubingShortage_InventoryUsage,[Item Name],[CurrentQty]),[CurrentQty])

[AlexisOlson]

This definitely looks odd but it's hard to diagnose without seeing your file. Can you share a file that enables us to replicate the issue (either share your existing file with anything sensitive removed or create a new dummy file)?

[Anonymous]

https://1drv.ms/u/s!AmgBMZ-nup2SglCZBGmVEND39B1p?e=j8K0ND 

Here is a copy of the sample data.

[AlexisOlson]

Thanks.

What's happening is that in your first table, it's grouping distinct Item Name and Current Qty combination. Some of these combinations appear multiple times though, which is why they add up to a larger number than you expect.

If you add DATECHG to the table, it becomes more obvious what's happening.

[Anonymous]

@AlexisOlson thank you.

So how would I solve the issue of summing the amounts to reflect the correct value regardless of the date?  The sum should be 130.

Thank you.

[AlexisOlson · Accepted solution]

It's doesn't make sense to me why that would necessarily be the "correct" sum but you could do something like this:

SUMX (
    SUMMARIZE ( Table1, Table1[Item Name], Table1[Current Qty] ),
    Table1[Current Qty]
)

[Anonymous · Accepted solution]

@AlexisOlson I was finally successful and was able to create the proper sum per item using this measure:

Current Qty = SUMX(SUMMARIZE(vw_TubingShortage_InventoryUsage,[Item Name],[CurrentQty]),[CurrentQty])