Se necesita ayuda: el total general no se muestra en la tabla de matriz

[Syndicate_Admin]

Hola

Necesito un poco de ayuda para que el total general aparezca en la parte inferior de mi tabla de Matrix. He creado una medida personalizada para mostrar la vista que necesito, pero por algún motivo, el total no se muestra correctamente. No pude hacer que funcionara. ¡Cualquier ayuda para arreglar esto sería realmente apreciada! Probablemente tendrá más sentido si puedes echar un vistazo a mi archivo de muestra.

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  
    )

Archivo de muestra
https://docs.google.com/document/d/1QZNBhYtGTD1NTI0NdaRXF0j3bP8t3J2k_sk9OqByKE0/edit?usp=sharing

¡Muchas gracias!

[Syndicate_Admin]

@Jihwan_Kim Muchas gracias, eso funciona. ¿Qué hiciste para solucionarlo? Realmente aprecio su ayuda.

[Syndicate_Admin]

Hola

Verifique la imagen a continuación y el archivo pbix adjunto para ver si resuelve el problema.

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
    )