Forum Discussion

WorldWide1's avatar
WorldWide1
Icon for Helper II rankHelper II
1 year ago
Solved

Calculation Help

Good Morning all -- Am stuck on a project and could use some help - thank you in advance for any thoughts.   In the attached workbook I have a table based on individual shipments, showing which Ca...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    You can create measures like

    Least Cost Shipment Count = 
VAR BaseTable = ADDCOLUMNS(
	VALUES( 'Ship History+Rates'[ShipmentID] ),
	"@least revenue carrier",
	CALCULATE(
		[Least Revenue Carrier],
		REMOVEFILTERS( 'Ship History+Rates'[SCAC/Rated] )
	)
)
VAR Result = SELECTCOLUMNS(
	FILTER(
		GROUPBY(
			BaseTable,
			[@least revenue carrier],
			"@num", SUMX( CURRENTGROUP(), 1 )
		),
		[@least revenue carrier] = SELECTEDVALUE( 'Ship History+Rates'[SCAC/Rated] )
	),
	[@num]
)
RETURN Result

    and

    Least Revenue Total = 
VAR BaseTable = ADDCOLUMNS(
	VALUES( 'Ship History+Rates'[ShipmentID] ),
	"@least revenue carrier",
	CALCULATE(
		[Least Revenue Carrier],
		REMOVEFILTERS( 'Ship History+Rates'[SCAC/Rated] )
	),
	"@least revenue value",
	CALCULATE(
		[Least Revenue$/Shipment],
		REMOVEFILTERS( 'Ship History+Rates'[SCAC/Rated] )
	)
)
VAR Result = SELECTCOLUMNS(
	FILTER(
		GROUPBY(
			BaseTable,
			[@least revenue carrier],
			"@val", SUMX( CURRENTGROUP(), [@least revenue value] )
		),
		[@least revenue carrier] = SELECTEDVALUE( 'Ship History+Rates'[SCAC/Rated] )
	),
	[@val]
)
RETURN Result

    Put those into a table with 'Ship History+Rates'[SCAC/Rated] and it should work

     

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
1 year ago

not quite understand your question. could you pls give us an example, if we select a Carrier, what's the expected output based on that selection?

WorldWide1's avatar
WorldWide1
Icon for Helper II rankHelper II
1 year ago

Hi Ryan --

What we have is an analyis of multiple rates returninig for the same shipment, and we're trying to find the Least Cost price on each shipment, subject to multiple Carriers being chosen throughout the data set.

So if you see the screenshots from previous messages, the first table, working correctly, shows the Least Cost price/Carrier on each ShipmentID.

However, in trying to build a summary table that shows how many shipments each Carrier would get (and what their Total Cost(price) would be on those shipments where they are the Least Cost(price) Carrier, we can't get that to populate correctly.  Result should look something like this, subject to the multiple selections chosen from the Carrier list.

Choose multiple Carriers:

Resulting table looks like this:

Thank you for your time.

Brian.