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

     

v-sdhruv's avatar
v-sdhruv
Icon for Community Support rankCommunity Support
1 year ago

Hi WorldWide1 ,
Since all your fields are measures here in the table, it would be difficult for you to summarize these because summarization takes columns as inputs in order to group by.
I would suggest you take up the raw field here, i.e SCAC/Rated and then create a summary table.
I have created a seperate measure to count new_carrier and taken Total as sum of Total Shipment Revenue.

count_carrier = COUNT('Ship History+Rates'[Carrier/Rated])

You can use these fields in the table and you will get the desired result.

Attached is the Pbix for your reference.
Hope this helps!
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!