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

     

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

Here is a different approach to your challenge. Instead of creating complex measures, i created a Fact table to specifically analyze the new carriers. Note that since its a fact table, it will only be refreshed when the semantic model refreshes.