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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors