Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
EnrichedUser
Helper III
Helper III

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.

 

EnrichedUser_1-1640994712912.png 

EnrichedUser_2-1640994759497.png

 

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION
EnrichedUser
Helper III
Helper III

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

View solution in original post

3 REPLIES 3
EnrichedUser
Helper III
Helper III

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
Helper III
Helper III

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:

EnrichedUser_0-1641009691263.png

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
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.