Forum Discussion

Zack92's avatar
Zack92
Helper III
1 year ago
Solved

Help Needed – Grand Total Not Showing in Matrix Table

Hello, I need a bit of help with getting the Grand Total to show up at the bottom of my Matrix table. I created a custom measure to display the view I need, but for some reason, the total isn’t ...
  • Jihwan_Kim's avatar
    1 year ago

    Hi,

    Please check the below picture and the attached pbix file whether it solves the issue.

     

    Selected Metric Value = 
    SUMX (
        VALUES ( Metrics[Metric] ),
        CALCULATE (
            SWITCH (
                SELECTEDVALUE ( Metrics[Metric] ),
                "Revenue", SUM ( nl_Sales[ext_price] ) / 1000,
                "Volume", SUM ( nl_Sales[inv_qty] ),
                "Gross Profit $", SUM ( nl_Sales[gp$] ) / 1000,
                "Gross Margin %",
                    IF (
                        [Gross Profit $] / [Revenue] > 1
                            || [Gross Profit $] / [Revenue] < 0,
                        BLANK (),
                        [Gross Profit $] / [Revenue]
                    ) * 100,
                BLANK ()
            )
        )
    )

     

    YoY % Change = 
    VAR CurrentValue =
        SUMX (
            VALUES ( Metrics[Metric] ),
            CALCULATE (
                SWITCH (
                    TRUE (),
                    SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue],
                    SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume],
                    SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $],
                    SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %],
                    BLANK ()
                )
            )
        )
    VAR LastPeriodValue =
        SUMX (
            VALUES ( Metrics[Metric] ),
            CALCULATE (
                SWITCH (
                    TRUE (),
                    ISFILTERED ( 'Date'[Date].[Quarter] ) || ISFILTERED ( 'Date'[Date].[Month] ),
                        CALCULATE (
                            SWITCH (
                                TRUE (),
                                SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue],
                                SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume],
                                SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $],
                                SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %],
                                BLANK ()
                            ),
                            PARALLELPERIOD ( 'Date'[Date], -1, QUARTER )
                        ),
                    ISFILTERED ( 'Date'[Date].[Year] ),
                        CALCULATE (
                            SWITCH (
                                TRUE (),
                                SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue],
                                SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume],
                                SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $],
                                SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %],
                                BLANK ()
                            ),
                            SAMEPERIODLASTYEAR ( 'Date'[Date] )
                        ),
                    BLANK ()
                )
            )
        )
    RETURN
        IF (
            ISBLANK ( LastPeriodValue ),
            BLANK (),
            - DIVIDE ( CurrentValue - LastPeriodValue, LastPeriodValue, 0 ) * 100
        )