Forum Discussion

EnrichedUser's avatar
EnrichedUser
Helper III
3 years ago

Incorrect Totals: - Matrix

Hi, 

I have an issue with a measure(s) that I have exhuasted all other resources to solve. 

 

I am trying to solve for "Monthly Inflation" which is defined as:
( [Last Unit Cost] - [Base Unit Cost] ) * [Average Monthly Usage]

I have included all relevent information below to create a sample model of the data as I cannot share the file. 

 

The receipt data contains only transactions that occur, but I am attempting to calaculate a value when there is no data. My usual sumx, addcolumns, summarize method is not working as I cannot seem to create a virtual table where all 12 months are included. 

 

Any help would be greatly apprecaited!

 

Table1: Receipts

OrderLineEff DateUnit CostExtended CostQuantitySite_Item_Key
12341449980.0979.7100SFA250
12351450160.0979.7100SFA250
12362451030.093279.327100SFA250
123714501992.71927.110SFA479
123814505792.71927.110SFA479
123914509926.388045.9305SFA479
124014493854.26325.566SFA479


Table2: Base Cost

Site_Item_KeyBase Cost
SFA2500.1011
SFA47954.26


Table3: Demand

N360 Days DemandSite_Item_Key
300SFA250
309SFA479

Table4: Date

Measures:

 

AMU = 
CALCULATE(
    SUM('Demand'[N360 Days Demand]) / 12,
    CROSSFILTER(Receipts[Site_Item_Key], 'Demand'[Site_Item_Key], Both),
    ALL('DateTable')
)

Avg Unit Cost (R) = 
DIVIDE( [Receipt COGS], [Receipt QTY], BLANK() )

Base Cost (R) = 
CALCULATE(
    SELECTEDVALUE( 'Base Cost'[Base Cost] ),
    CROSSFILTER( 'Base Cost'[Site_Item_Key], Receipts[Site_Item_Key], Both )
)
---------------------------------------------------------------------------------------------------------------------------------------
Last Unit Cost (R) = 
VAR LastMonthWithReceipts =
    CALCULATE(
        MAX('DateTable'[Month Year No]),
        FILTER( ALLSELECTED( 'Receipts' ),
        'Receipts'[Eff Date] <= MAX('DateTable'[Date])
        )
    )
VAR ReceiptCost = 
    CALCULATE(
        [Avg Unit Cost (R)],
        FILTER( ALLSELECTED( 'DateTable' ),
        'DateTable'[Month Year No] = LastMonthWithReceipts
    )
    )
RETURN ReceiptCost
---------------------------------------------------------------------------------------------------------------------------------------
Monthly Inflation (R) = 
VAR BaseUnitCost = CALCULATE( [Base Cost (R)], ALL( 'DateTable' ) )
VAR Result = ( [Last Unit Cost (R)] - BaseUnitCost ) * [AMU]
RETURN Result
---------------------------------------------------------------------------------------------------------------------------------------
Receipt COGS = 
SUM( Receipts[Extended Cost] )
---------------------------------------------------------------------------------------------------------------------------------------
Receipt QTY = 
SUM( Receipts[Quantity] )
---------------------------------------------------------------------------------------------------------------------------------------

 

 

 

DateTable = 
ADDCOLUMNS (
   CALENDAR ( DATE(2023,1,1), DATE(2023,12,31) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Month No", MONTH ( [Date] ),
    "Quarter", "Q" & CEILING ( MONTH ( [Date] ) / 3, 1 ),
    "Week No", WEEKNUM ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday No", WEEKDAY ( [Date], 2 ) + 1,
    "Month Year", FORMAT ( [Date], "MMM yyyy" ),
    "Month Year No", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Week Year", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),
    "Month Sort", YEAR ( [Date] ) * 10000 + MONTH ( [Date] ),
    "Week Sort", YEAR ( [Date] ) * 10000 + WEEKNUM ( [Date] )
)

 

Model:
Expected Output:

Site_Item_KeyMonth YearAMUBase Cost (R)Avg Unit Cost (R)Last Unit Cost (R)Monthly Inflation (R)Comments
SFA250Jan-23250.1011  $0.00Zero Because Last Unit Cost is assummed to be equal to Base Cost
SFA250Feb-23250.1011  $0.00Zero Because Last Unit Cost is assummed to be equal to Base Cost
SFA250Mar-23250.10110.0970.097($0.10)( Last Unit Cost (0.097) - Base Cost (0.1011) ) * AMU (25)
SFA250Apr-23250.1011 0.097($0.10)( Last Unit Cost - Base Cost  ) * AMU
SFA250May-23250.1011 0.097($0.10)( Last Unit Cost - Base Cost  ) * AMU
SFA250Jun-23250.10110.093270.093($0.20)( Last Unit Cost - Base Cost  ) * AMU
SFA250Jul-23250.1011 0.093($0.20)( Last Unit Cost - Base Cost  ) * AMU
SFA250Aug-23250.1011 0.093($0.20)( Last Unit Cost - Base Cost  ) * AMU
SFA250Sep-23250.1011 0.093($0.20)( Last Unit Cost - Base Cost  ) * AMU
SFA250Oct-23250.1011 0.093($0.20)( Last Unit Cost - Base Cost  ) * AMU
SFA250Nov-23250.1011 0.093($0.20)( Last Unit Cost - Base Cost  ) * AMU
SFA250Dec-23250.1011 0.093($0.20)( Last Unit Cost - Base Cost  ) * AMU
      ($1.67)Sum of Monthly Inflation Jan to Dec
SFA479Jan-2325.7554.2654.2654.260$0.00( Last Unit Cost (54.260) - Base Cost (54.26) ) * AMU (25.75)
SFA479Feb-2325.75  54.260$1,397.20( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Mar-2325.75   $0.00( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Apr-2325.7554.2692.7192.710$990.09( Last Unit Cost  - Base Cost  ) * AMU 
SFA479May-2325.7554.2692.7192.710$990.09( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Jun-2325.7554.2626.3826.380($717.91)( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Jul-2325.75  26.380$679.29( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Aug-2325.75  26.380$679.29( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Sep-2325.75  26.380$679.29( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Oct-2325.75  26.380$679.29( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Nov-2325.75  26.380$679.29( Last Unit Cost  - Base Cost  ) * AMU 
SFA479Dec-2325.75  26.380$679.29( Last Unit Cost  - Base Cost  ) * AMU 
      $6,735.17Sum of Monthly Inflation Jan to Dec
      $6,733.50Grand Total

3 Replies

  • I have made significant progress, but am not seeing the totals sum up correctly for the year month now.

    Monthly Inflation (TEST2) = 
    VAR VirtualTable = 
    ADDCOLUMNS(
        SUMMARIZE(
            GENERATE(
                VALUES(Receipts[Site_Item_Key]),
                VALUES('Date'[Year Month])
            ),
            Receipts[Site_Item_Key],
            'Date'[Year Month]
        ),
        "@Monthly Inflation", [Monthly Inflation (R)]
    )
    VAR VirtualTable2 = 
    ADDCOLUMNS(
        SUMMARIZE(
            GENERATE(
                VALUES(Receipts[Site_Item_Key]),
                VALUES('Date'[Year Month])
            ),
            Receipts[Site_Item_Key],
            'Date'[Year Month]
        ),
        "@Monthly Inflation", [Monthly Inflation (R)]
    )
    RETURN
    SWITCH(
        TRUE(),
        ISINSCOPE( Receipts[Site_Item_Key] ) && ISINSCOPE( 'Date'[Year Month] ), [Monthly Inflation (R)],
        ISINSCOPE( Receipts[Site_Item_Key] ), SUMX( VirtualTable, [@Monthly Inflation] ) ,
        ISINSCOPE( 'Date'[Year Month] ), SUMX( VirtualTable2, [@Monthly Inflation] ), -- HELP
        SUMX( VirtualTable, [@Monthly Inflation] )
    )

     

    The totals for each year month are wrong.

     

  • EnrichedUser , Try a measure like this

    Sumx(summarize(Table, Table[Order], Table[Line], Table[Eff Date], "_1",( [Last Unit Cost] - [Base Unit Cost] ) * [Average Monthly Usage]), [_1])

     

    if needed add additional column from visual

  • Thanks for the attempt, but was not sucessful. 

     

    Here is my latest go:

    Monthly Inflation (TEST) = 
    VAR VirtualTable = 
    ADDCOLUMNS(
        SUMMARIZE(
            GENERATE(
                VALUES(Receipts[Site_Item_Key]),
                VALUES('Date'[Year Month])
            ),
            Receipts[Site_Item_Key],
            'Date'[Year Month]
        ),
        "@Monthly Inflation", [Monthly Inflation (R)]
    )
    RETURN
    IF(
        ISINSCOPE( Receipts[Site_Item_Key] ) && ISINSCOPE( 'Date'[Year Month] ), 
        [Monthly Inflation (R)],
        SUMX( VirtualTable, [@Monthly Inflation] )
    )