Suming a measure

[Gustavo98]

HI everyone,

I want to sum "Multiplicando" column. I tried to do the sum in the column "Sumando Multiplicando" but i failed.

I tried this measure:  

Sumando Multiplicando = SUMX( ADDCOLUMNS( SUMMARIZE( Combinar1, Combinar1[NumSem], Combinar1[AñoySem]), "Columna1", [Multiplicando]),[Columna1])

I hope someone can help me.

[PurpleGate]

Maybe try

measure= sumx(table([ID]),[multiplicando])

the table[ID] refers to whatever you are using to make the rows in your table. I guess it could be ('table'[NumSem]) or ('table'[AñoySem])

[Gustavo98]

I tried that and appears :

No se puede determinar un valor único para la columna 'NumSem' en la tabla 'Combinar1'. Esto puede suceder cuando una fórmula de medida hace referencia a una columna que contiene muchos valores sin especificar una agregación, como min, max, count o sum, para obtener un resultado único.

Translated to english : 

Cannot determine a unique value for column 'SemNum' in table 'Join1'. This can happen when a measure formula references a column that contains many values ​​without specifying an aggregation, such as min, max, count, or sum, to get a single result.

[PurpleGate]

Maybe take out the column name and just have

Measure = SUMX(Combinar1,[multiplicando])

[Gustavo98]

iT gives a result but a wrong one. 

The result was 0,25. But if you sum "Multiplicando" column manually we can see that is not true.  The correct result should be 1,31. I have been trying some measures and all are given me the same incorrect result 0,25. Im going to try filtering first by a measure 

My tries are: 

Sumando Multipl = SUMX( ADDCOLUMNS( SUMMARIZE( Combinar1, Combinar1[NumSem], Combinar1[AñoySem]), "Columna1", [Var Kilos - Var Kilos Promedio] * [Var Precio - Var Precio Promedio] ),[Columna1])

Sumando Multiplicando = SUMX( ADDCOLUMNS( SUMMARIZE( Combinar1, Combinar1[NumSem], Combinar1[AñoySem]), "Columna1", [Multiplicando]),[Columna1])

[Whitewater100]

Hi:

Can you share an example file? Thanks..

[Gustavo98]

Im going to share a file as soon as i can.

Ty.

[Whitewater100]

Hi:

That will be helpful, even if it's a few rows from each table involved. Thanks!

[Gustavo98]

https://app.powerbi.com/reportEmbed?reportId=58e1ccf1-4df9-4198-a63d-cd9fc82a5787&autoAuth=true&ctid...

Here is a example file. The important sheet is "Tabla principal mejorada"

Can you see it?

[Whitewater100]

Hi:

I still can not see it. Can you save it to google drive or dropbox and send it that way, as a link?

Thanks!

Bill

[Gustavo98]

Remember that i want to create a measure that allows me to sum "Multiplicando" column. All columns to the right of "Multiplicando" column are tries that i did to sum that column, sadly my tries were unsuccesfull.

Thanks for helping me.

[Whitewater100]

Hi:

If you are using measure as filter is a single visual you can try something like this(example):

[Whitewater100]

Hello Gustavo:

I was able to open your file but there is no data in Table Medidas. I beleive you have a SUMX total issue. I will paste a couple of examples how to write the code for this type of problem.

Single Measure = SUM(_sUMxTABLE[amount]) -1000

Total Measure =

var vtable = SUMMARIZE(_sUMxTABLE,

               _sUMxTABLE[ID],

               "AMT", [single measure])

return

IF(HASONEVALUE(_sUMxTABLE[ID]),[Single Measure], SUMX(vtable,[AMT]))

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

So, if we have a measure like:

m_Single = SUM(Table1[Value])-50

This measure will cause problems in total lines. So, if we are summarizing by [Name], we create this measure:

m_Total 1 =

VAR __table = SUMMARIZE('Table1',[Name],"__value",[m_Single])

RETURN

IF(HASONEVALUE(Table1[Name]),[m_Single],SUMX(__table,[__value]))

If we are summarizing by [Category1], we create this measure:

m_Total 2 =

VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single])

RETURN

IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

NEXT EXAMPLE w SUMMARIZE:

sum Count of LINENUM =

SUMX (

    SUMMARIZE (

        LEM_STATUS_ALL_V,

        LEM_STATUS_ALL_V[WAPPRDATE],

        LEM_STATUS_ALL_V[WAPPRBY],

        LEM_STATUS_ALL_V[SUBMITBY]

    ),

    CALCULATE (

        DISTINCTCOUNT ( 'LEM_STATUS_ALL_V'[LINENUM] ),

        'LEM_STATUS_ALL_V'[LEMNUM]

    )

)

I hope the language translates OK for you.

Thank you..

[Gustavo98]

I tried the first option i didnt work.

The measure "Multiplicando" that i want to sum is this : 

Multiplicando = [Var Kilos - Var Kilos Promedio] * [Var Precio - Var Precio Promedio] * [Measure Filter]

Remember the first to columns are not measures, everthing else are measures. Im going to still trying, ty vry much Whitewater100

[Gustavo98]

Table "Medidas" is a table that i created to store my measure, is normal if you dont see columns.

Im going to read your answer, ty vry much.

[Gustavo98]

Now i think will work

https://drive.google.com/file/d/1CJOCZs7VA-_dPH2pE30lYO3qnqGkeN3Y/view?usp=sharing

[Whitewater100]

Hello:

What happens if you just do:

Sumando Multiplicando = SUM(TableName[Multiplicando])?

[Gustavo98]

I forgot say that "Multiplicando" is a measure: 

Multiplicando = [Var Kilos - Var Kilos Promedio] * [Var Precio - Var Precio Promedio] * [Measure Filter]

Ty for responding 😉