Calculation Help

[WorldWide1]

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 Carrier would carry that shipment based on the Least Revenue $, and the PBI table that is working is subject to the WWEX Carrier filter in the upper right.  Meaning, if the Carrier choices change in the filter, the table will update w/the next Carrier in line price-wise.  Works great.

What's not working is when I try to create a table on the lower right that summarizes the New Carriers, how many shipments they would get, each Carrier's new Total Revenue, etc.

Should look like this:

But the only thing I've been able to show in the summary table is the single Least Revenue Carrier.

Don't see where I can attach my PBI workbook?

Any help is very much appreciated.

Thank you.

[johnt75 · Accepted solution]

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

[WorldWide1]

That worked!  Thank you very much for the help!

[v-sdhruv]

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]

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:

[ryan_mayu]

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]

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.

[WorldWide1]

https://drive.google.com/file/d/1aIHBTt5katrkMmOmEZg1vh8eEhhdk6hl/view?usp=sharing

[Tutu_in_YYC]

Try using dropbox or google drive to provide your pbix.

[WorldWide1]

https://drive.google.com/file/d/1aIHBTt5katrkMmOmEZg1vh8eEhhdk6hl/view?usp=drive_linkhttps://drive.google.com/file/d/1aIHBTt5katrkMmOmEZg1vh8eEhhdk6hl/view?usp=drive_link Hopefully this will work as still looking for assistance.  Thank you.

[Tutu_in_YYC]

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.

[WorldWide1]

Thank you.  I really love your approach.

However, doesn't quite do what we need.  When all Carriers are Selected, it's good.

But the tables are not updating responding when we choose different carriers.  For example, we still have the problem that when we remove Carriers from the Selection, the tables are not finding the next Least Cost Carrier on each ShipmentID and updating the results.

All Carriers Selected:

Now, with CTII removed from the Selection, it just removes CTII from the table, it doesn't update by finding the next Least Cost Carrier - we should still have 96 shipments after CTII is removed, but the Carrier counts and Least Revenue Totals should adjust according to the remaining Carriers:

I do appreciate any further assistance you have on this.

Thanks.

Brian.

[Tutu_in_YYC]

This looks promising. Have a look.

[WorldWide1]

Thank you - my company doesn't have the latest version of PBI yet apparently so I can't open your most recent workbook.  can you save it in an earlier version?  if not I'll get w/my IT people.  Thank you.

[Tutu_in_YYC]

Oops, odd that you could open the previous one though. Unfortunately I am not able to save in earlier version.... IT to the rescue!

[WorldWide1]

Looks like the extensive calculations in one of the responses above was the one to go with.  Thank you so much for your efforts!

[Tutu_in_YYC]

Thanks for the explanation. In this case, measures are the way to go. I will have a look.