Forum Discussion

WorldWide1's avatar
WorldWide1
Helper 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
Community 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!

  • WorldWide1's avatar
    WorldWide1
    Helper II
    1 year ago

    Thank you for your help.  The book you returned, however, is showing the total number of shipments each Carrier provided a rate for, and also the Total Revenue Sum on all of those shipments.

    What we need to show tho is just the number of times each Carrier is the Least Cost Carrier (among all of the Rates returned on each ShipmentID), subject to the Carriers chosen from the WWEX Carriers list/filter.

     

    The result we're after is just counting the instances that each New Carrier shows in the table that is broken out by ShipID:  

    So in the sample data PBI previously attached, the result for the Summary table should be something like this: