Forum Discussion
WorldWide1
Helper II
1 year agoCalculation 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...
- 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 Resultand
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 ResultPut those into a table with 'Ship History+Rates'[SCAC/Rated] and it should work
johnt75
Super User
1 year agoYou 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
- WorldWide11 year ago
Helper II
That worked! Thank you very much for the help!