Total de una medida de producto suma no coincide

[Afleon]

Buena tarde,

si alguien me puede ayudar o aclarar lo siguiente se lo agradezco. En Excel realizo la SUMAPRODUCTO entre dos matrices y el resultado es: 328,709,908.20 al realizar el calculo en Power Bi con DAX el resultado es el siguiente: 328,681,143.58, como se observa es una pequeña diferencia de 28.764.62.

La forma com realizo el calculo de sumaproducto en PowerBI es:

Producto Matriz - Bolsa =

SUMX(

    'Matriz Consumo Activa',

    SUMX(

        FILTER(

            ALL('Precio Bolsa'),

            'Precio Bolsa'[Fecha] = 'Matriz Consumo Activa'[Fecha]

        ),

        'Matriz Consumo Activa'[H1] * 'Precio Bolsa'[H1] +

        'Matriz Consumo Activa'[H2] * 'Precio Bolsa'[H2] +

        'Matriz Consumo Activa'[H3] * 'Precio Bolsa'[H3] +

        'Matriz Consumo Activa'[H4] * 'Precio Bolsa'[H4] +

        'Matriz Consumo Activa'[H5] * 'Precio Bolsa'[H5] +

        'Matriz Consumo Activa'[H6] * 'Precio Bolsa'[H6] +

        'Matriz Consumo Activa'[H7] * 'Precio Bolsa'[H7] +

        'Matriz Consumo Activa'[H8] * 'Precio Bolsa'[H8] +

        'Matriz Consumo Activa'[H9] * 'Precio Bolsa'[H9] +

        'Matriz Consumo Activa'[H10] * 'Precio Bolsa'[H10] +

        'Matriz Consumo Activa'[H11] * 'Precio Bolsa'[H11] +

        'Matriz Consumo Activa'[H12] * 'Precio Bolsa'[H12] +

        'Matriz Consumo Activa'[H13] * 'Precio Bolsa'[H13] +

        'Matriz Consumo Activa'[H14] * 'Precio Bolsa'[H14] +

        'Matriz Consumo Activa'[H15] * 'Precio Bolsa'[H15] +

        'Matriz Consumo Activa'[H16] * 'Precio Bolsa'[H16] +

        'Matriz Consumo Activa'[H17] * 'Precio Bolsa'[H17] +

        'Matriz Consumo Activa'[H18] * 'Precio Bolsa'[H18] +

        'Matriz Consumo Activa'[H19] * 'Precio Bolsa'[H19] +

        'Matriz Consumo Activa'[H20] * 'Precio Bolsa'[H20] +

        'Matriz Consumo Activa'[H21] * 'Precio Bolsa'[H21] +

        'Matriz Consumo Activa'[H22] * 'Precio Bolsa'[H12] +

        'Matriz Consumo Activa'[H23] * 'Precio Bolsa'[H23] +

        'Matriz Consumo Activa'[H24] * 'Precio Bolsa'[H24]

        )

            )  

 Mis dudas son:

1. ¿Es normal esa diferencia entre la operación realizada en Excel y la de PowerBI?
2. ¿Estaré realizando mal el calculo en PowerBI?, en caso afirmativo como puedo ajustar el cálculo

De antemano aprecia la ayuda y claridad que me puedan brindar al respecto.

Feliz día.

AFLD

[v-xinruzhu-msft · Accepted solution]

Hi @Afleon 

You can consider to unpivot your columns([H1]-[H24]) first in power query, you can refer to the following link about unpivot

Unpivot columns - Power Query | Microsoft Learn

e.g Then new column of the ([H1]-[H24]) named 'Attribute', the column of the related value named'Value' , like the following picture

Then create a Measure

Measure =
VAR a =
    MAXX (
        FILTER (
            'Matriz Consumo Activa',
            [Fecha]
                IN VALUES ( 'Precio Bolsa'[Fecha] )
                    && [Attribute] IN VALUES ( 'Precio Bolsa'[Attribute] )
        ),
        [Value]
    )
RETURN
    a * SUM ( 'Precio Bolsa'[Value] )

Then create a Measure1

Measure1=SUMX('Precio Bolsa',[Measure])

Then put the Measure1 to the related visual

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Afleon , As difference is small, are you using rounding in excel while doing mutiplication ?

Try without all or use allselected

SUMX( 'Active Consumption Matrix' , SUMX( FILTER( ( 'Stock Price' ), 'Stock Market Price' [Date] = 'Active Consumption Matrix' [Date] ), 'Active Consumption Matrix' [H1] * 'Stock Market Price' [H1] + 'Active Consumption Matrix' [H2] * 'Stock Market Price' [H2] + 'Active Consumption Matrix' [H3] * 'Stock Market Price' [H3] + 'Active Consumption Matrix' [H4] * 'Stock Market Price' [H4] + 'Active Consumption Matrix' [H5] * 'Stock Market Price' [H5] + 'Active Consumption Matrix' [H6] * 'Stock Market Price' [H6] + 'Active Consumption Matrix' [H7] * 'Stock Market Price' [H7] + 'Active Consumption Matrix' [H8] * 'Stock Market Price' [H8] + 'Active Consumption Matrix' [H9] * 'Stock Market Price' [H9] + 'Active Consumption Matrix' [H10] * 'Stock Market Price' [H10] + 'Active Consumption Matrix' [H11] * 'Stock Market Price' [H11] + 'Active Consumption Matrix' [H12] * 'Stock Market Price' [H12] + 'Active Consumption Matrix' [H13] * 'Stock Market Price' [H13] + 'Active Consumption Matrix' [H14] * 'Stock Market Price' [H14] + 'Active Consumption Matrix' [H15] * 'Stock Market Price' [H15] + 'Active Consumption Matrix' [H16] * 'Stock Market Price' [H16] + 'Active Consumption Matrix' [H17] * 'Stock Market Price' [H17] + 'Active Consumption Matrix' [H18] * 'Stock Market Price' [H18] + 'Active Consumption Matrix' [H19] * 'Stock Market Price' [H19] + 'Active Consumption Matrix' [H20] * 'Stock Market Price' [H20] + 'Active Consumption Matrix' [H21] * 'Stock Market Price' [H21] + 'Active Consumption Matrix' [H22] * 'Stock Market Price' [H12] + 'Active Consumption Matrix' [H23] * 'Stock Market Price' [H23] + 'Active Consumption Matrix' [H24] * 'Stock Market Price' [H24] ) )

SUMX( 'Active Consumption Matrix' , SUMX( FILTER( ALLselected ( 'Stock Price' ), 'Stock Market Price' [Date] = 'Active Consumption Matrix' [Date] ), 'Active Consumption Matrix' [H1] * 'Stock Market Price' [H1] + 'Active Consumption Matrix' [H2] * 'Stock Market Price' [H2] + 'Active Consumption Matrix' [H3] * 'Stock Market Price' [H3] + 'Active Consumption Matrix' [H4] * 'Stock Market Price' [H4] + 'Active Consumption Matrix' [H5] * 'Stock Market Price' [H5] + 'Active Consumption Matrix' [H6] * 'Stock Market Price' [H6] + 'Active Consumption Matrix' [H7] * 'Stock Market Price' [H7] + 'Active Consumption Matrix' [H8] * 'Stock Market Price' [H8] + 'Active Consumption Matrix' [H9] * 'Stock Market Price' [H9] + 'Active Consumption Matrix' [H10] * 'Stock Market Price' [H10] + 'Active Consumption Matrix' [H11] * 'Stock Market Price' [H11] + 'Active Consumption Matrix' [H12] * 'Stock Market Price' [H12] + 'Active Consumption Matrix' [H13] * 'Stock Market Price' [H13] + 'Active Consumption Matrix' [H14] * 'Stock Market Price' [H14] + 'Active Consumption Matrix' [H15] * 'Stock Market Price' [H15] + 'Active Consumption Matrix' [H16] * 'Stock Market Price' [H16] + 'Active Consumption Matrix' [H17] * 'Stock Market Price' [H17] + 'Active Consumption Matrix' [H18] * 'Stock Market Price' [H18] + 'Active Consumption Matrix' [H19] * 'Stock Market Price' [H19] + 'Active Consumption Matrix' [H20] * 'Stock Market Price' [H20] + 'Active Consumption Matrix' [H21] * 'Stock Market Price' [H21] + 'Active Consumption Matrix' [H22] * 'Stock Market Price' [H12] + 'Active Consumption Matrix' [H23] * 'Stock Market Price' [H23] + 'Active Consumption Matrix' [H24] * 'Stock Market Price' [H24] ) )

[Afleon]

@amitchandak, Gracias por responder, en excel solamente estoy usando la siguiente formula:

=SUMAPRODUCTO(MatrizConsumo[[H1]:[H24]],MatrizBolsaD37:AA67) el resultado que arroja es 328,709,908.20 y son los mismos registros. En powerBi la operación la realizo desde una lista de Sharepoint.

Realicé los cambios que me sugeriste pero el resultado no cambia en nada.