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

     

Tutu_in_YYC's avatar
Tutu_in_YYC
Icon for Super User rankSuper User
1 year ago

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's avatar
    WorldWide1
    Icon for Helper II rankHelper II
    1 year ago

    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's avatar
      Tutu_in_YYC
      Icon for Super User rankSuper User
      1 year ago

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

      • WorldWide1's avatar
        WorldWide1
        Icon for Helper II rankHelper II
        1 year ago

        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.