Forum Discussion

EnrichedUser's avatar
EnrichedUser
Helper III
4 years ago
Solved

Incorrect Totals - Matrix

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

 

 

 

 

 

 

Need Help
  • EnrichedUser's avatar
    EnrichedUser
    4 years ago
    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

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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]
)
  • EnrichedUser's avatar
    EnrichedUser
    Helper III
    4 years ago

    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])
    )
)

     

  • EnrichedUser's avatar
    EnrichedUser
    Helper III
    4 years ago
    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