Incorrect Totals - Matrix

[EnrichedUser]

Good Day!

I think I have a rather complex situation and would greatly apprecaite some help. 

Specifically, I am not able to correctly determine the row, column or grand totals at the 'Site' level/context. The only thing that does correctly show is the row total in the context of the item number.

 

I have based this attempt off Enterprise DNA's youtube video:
https://www.youtube.com/watch?v=eoT-jPzV3Wk&ab_channel=EnterpriseDNA

https://forum.enterprisedna.co/t/pbix-file-for-fixing-matrix-totals-youtube-video/17559

I think my virtual table 'vTable' is not correct. The invoice history table provides line level detail and has well over 1mn records. The expected output would be a matrix filterable by site and item with correct totals.

I have also tried looking at custom visuals such as Acterys Matrix Light but its does not allow for enough data to be displayed at once.

Lastly, I cannot provide a pbix file. 

Customer Inflation with Fixed Totals (not working) = 
VAR vTable =
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES('Date'[Month]),
            VALUES('Invoice History'[Item Number])
        ),
        "@inflation", [Customer Inflation]
    )
VAR TotalInflation =
    SWITCH(
        TRUE(),
        HASONEVALUE('Date'[Month]) && (HASONEVALUE('Invoice History'[Site]) || HASONEVALUE('Invoice History'[Item Number])),
            [Customer Inflation],   // Base data
        HASONEVALUE('Date'[Month]),  
            CALCULATE(
                SUMX( vTable,
                [@inflation]
                ),
            VALUES('Invoice History'[Item Number])
            ),                                                                      // column totals,
        HASONEVALUE('Invoice History'[Item Number]),                                // row totals
            CALCULATE(
                SUMX(
                    vTable,
                    [@inflation]
                ),
                VALUES('Date'[Month])
            ),                                                                      // grand total
        SUMX(
            vTable,
            [@inflation]
        )
    )
RETURN
    TotalInflation

[EnrichedUser · Accepted solution]

Customer Inflation with Fixed Totals = 
VAR vTable = 
ADDCOLUMNS(
    SUMMARIZE('Invoice History',
        'Date'[Fiscal Year Number],
        'Date'[Month],
        'Invoice History'[Site],
        'Invoice History'[Item Number]
    ),
    "@inflation", [Customer Inflation]
)
VAR TotalInflation =
    SWITCH(
        TRUE(),
        HASONEVALUE('Date'[Month]) && HASONEVALUE('Invoice History'[Item Number]),
            [Customer Inflation],   // Base data
        HASONEVALUE('Date'[Month]),  
            CALCULATE(
                SUMX( vTable,
                [@inflation]
                )
            ),                                                                      // column totals,
        HASONEVALUE('Invoice History'[Item Number]),                                // row totals
            CALCULATE(
                SUMX(
                    vTable,
                    [@inflation]
                ),
                VALUES('Date'[Month])
            ),                                                                      // grand total
        SUMX(
            vTable,
            [@inflation]
        )
    )
RETURN
    TotalInflation

Working solution

[EnrichedUser]

Thank you so much for the reply! My invoice table does have a relationship to the date table, you are correct. I had previous tried variations of using summarize.

Your summarize provides the following:

The crossjoin is the same as well. 

The reason I was using the variable table was because the measure [Customer Inflation] is using a running total. It does a comparison to the current month to the previous ones within the year. So for the totals, there is no context to compare for the measure. 

Sales RT = 
CALCULATE(
    [Total Sales],
    FILTER(
        ALLSELECTED('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

[AlexisOlson]

I don't understand why all these cases are necessary. What's wrong with the following?

SUMX (
    CROSSJOIN (
        VALUES ( 'Date'[Month] ),
        VALUES ( 'Invoice History'[Item Number] )
    ),
    [Customer Inflation]
)

If your invoice table has a relationship to the date table, then a more efficient version might work:

SUMX (
    SUMMARIZE (
        'Invoice History',
        'Invoice History'[Item Number],
        'Date'[Month]
    ),
    [Customer Inflation]
)