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

Try using dropbox or google drive to provide your pbix.

    • 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.