Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Fred LaValley]

?i’m getting my butt kicked… 12+ hours on the same issue and i think i’m making it more complicated than it needs to be. Please help!

Previously in BI, I was able to ‘split columns by delimiter’ which enabled me to isolate each Sales Order Number and parse it down into it’s own row. However, when I did this, it copied all the rest of the data in the original row with it, which is now giving me problems with the Total Freight cost. I need to be able to divide the total freight cost between the various Sales Orders that shipped together in one truckload. Up to 4 or 5 Sales Orders sometimes ship together on one truck. I know how to do it in Excel using COUNTIF and referencing the Invoice number (there’s only one Invoice number per truck), but I am struggling with how to do it in BI.

B	C	D	E	F
Sales Order Number	Invoice Num	Origin Pickup Name	Destination Delivery Name	Total Charges
1432489	6111205518	Cold Storage	Cold Storage Customer	$4,366.18
1434478	6111205518	Cold Storage	Cold Storage Customer	$4,366.18
1438277	6111205518	Cold Storage	Cold Storage Customer	$4,366.18
050-8022	6116028056	X Facility	X Customer	552.83
1457283	6116028056	X Facility	X Customer	552.83

My problem is Invoice 6111205518 really only cost $4,366.18, but after splitting it and parsing it down, when I SUM it, the total is $13,098.54. Same thing for 6116028056; Total Charges is really only $552.83 for the entire Invoice, not each Order Number.

Using Excel, these would be the correct amounts:

=$F3/COUNTIF($C:$C,$C3)

Cold Storage Customer	$1,455.39
Cold Storage Customer	$1,455.39
Cold Storage Customer	$1,455.39
X Customer	$276.42
X Customer	$276.42

How do I do this in BI? How do i create a measure for this?

Originally all the Sales Orders on the same Invoice appear in one cell together, separated by commas. Can I add the solution to my Query when splitting the Sales Orders by delimiter and parsing it down?

——————————
F.L.
Oakwood, GA
——————————

[Ben Howard]

As a start you could create a new table using the Groupby or summarize functions.  https://docs.microsoft.com/en-us/dax/groupby-function-dax 

Attached is a pbix which shows you how it’s done using your data.

——————————
Ben Howard
——————————
——————————————-

[Fred LaValley]

That’s awesome! I’ve played around with it some now, but I don’t see how it can give me what I’m really after: breaking down the Total Cost to the Sales Order level. Basically dividing the Total Cost by how many times that specific invoice number appears (COUNTIF).

 

The GROUBY is good for the overall cost at the invoice level, but when I expand my data to show the Sales Orders, it still repeats the total charges and therefore shows double and triple the charges since each sales order is still showing the full freight mount.

 

Fred LaValley

Oakwood, GA

 

——Original Message——

As a start you could create a new table using the Groupby or summarize functions.  https://docs.microsoft.com/en-us/dax/groupby-function-dax 

Attached is a pbix which shows you how it’s done using your data.

——————————
Ben Howard
——————————

[Ben Howard]

Hi,
I suspect you might have to approach this a different way, rather than how you would in Excel.  We’d need to see the model and data before we could help further, as it stands, we’re trying, and potentially succeeding, in solving a discrete problem, only to find that it doesn’t help in solving the bigger problem, which we know nothing about.

——————————
Ben Howard
——————————
——————————————-

[Fred LaValley]

yes, it is quite maddening for me and i’m sure for you at this point…but…

GOOD NEWS!!!! I figured it out thanks to GROUPBY! I used it to count how many times each invoice appeared, and then created this measure:

Div Freight by Row = SUM(Detail[Total Charges]) / PRODUCT(‘Invoice Number Count'[Count])

The Invoice Number Count was the GROUPBY that I created thanks to you! It does not give me the exact $ for freight, but it at least splits it out evenly so that the final amount is correct, and not duplicated.

Solved! Thank you!

 ?

——————————
F.L.
Oakwood, GA
——————————
——————————————-