Forum Discussion
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 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.
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
16 Replies
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
That worked! Thank you very much for the help!
Try using dropbox or google drive to provide your pbix.
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.
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.
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?
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.
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!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:
