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 showing correctly. I couldn’t get it to work. Any help fixing this would be really appreciated! It’ll probably make more sense if you can take a look at my sample file.

 

Metrics = DATATABLE(
    "Metric", STRING,
    "SortOrder", INTEGER,
    {
        {"Revenue", 1},
        {"Volume", 2},
        {"Gross Profit $", 3},
        {"Gross Margin %", 4}
    }
)
Value = 
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 = 
    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 = 
    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  
    )


Sample File 
https://drive.google.com/file/d/1F56ShHxhMJBHTJ25uA8F1ETDysRIzSnf/view?usp=sharing

 

Thanks so much!



Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    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
    )

2 Replies

  • Zack92's avatar
    Zack92
    Helper III
    1 year ago

    Jihwan_Kim Thank you so much, that works. What did you do to fix it? Really appreciate your help. 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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
    )