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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.