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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Afleon
Frequent Visitor

Total de una medida de producto suma no coincide

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

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1700115975208.png

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.

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1700115975208.png

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
Super User
Super User

@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] ) )

@amitchandakGracias 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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.