topic Re: Suming a measure in DAX Commands and Tips

Suming a measure

Anonymous — Sun, 21 Aug 2022 23:08:14 GMT

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.

Re: Suming a measure

Whitewater100 — Mon, 22 Aug 2022 00:42:21 GMT

Hello:

What happens if you just do:

Sumando Multiplicando = SUM(TableName[Multiplicando])?

Re: Suming a measure

Anonymous — Mon, 22 Aug 2022 01:24:29 GMT

I forgot say that "Multiplicando" is a measure:

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

Ty for responding 😉

Re: Suming a measure

PurpleGate — Mon, 22 Aug 2022 11:05:08 GMT

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

Re: Suming a measure

Anonymous — Mon, 22 Aug 2022 13:11:49 GMT

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.

Re: Suming a measure

PurpleGate — Mon, 22 Aug 2022 13:24:44 GMT

Maybe take out the column name and just have

Measure = SUMX(Combinar1,[multiplicando])

Re: Suming a measure

Anonymous — Mon, 22 Aug 2022 13:39:31 GMT

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

Re: Suming a measure

Whitewater100 — Mon, 22 Aug 2022 18:41:49 GMT

Hi:

Can you share an example file? Thanks..

Re: Suming a measure

Anonymous — Mon, 22 Aug 2022 22:00:06 GMT

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

Ty.

Re: Suming a measure

Whitewater100 — Tue, 23 Aug 2022 20:25:41 GMT

Hi:

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

Re: Suming a measure

Anonymous — Fri, 26 Aug 2022 19:31:30 GMT

https://app.powerbi.com/reportEmbed?reportId=58e1ccf1-4df9-4198-a63d-cd9fc82a5787&autoAuth=true&ctid=c5338e3f-9c2c-4a29-8f4e-a61566ea3e78

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

Can you see it?

Re: Suming a measure

Whitewater100 — Fri, 26 Aug 2022 19:36:41 GMT

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

Re: Suming a measure

Anonymous — Fri, 26 Aug 2022 20:08:50 GMT

Now i think will work

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

Re: Suming a measure

Anonymous — Fri, 26 Aug 2022 20:13:27 GMT

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.

Re: Suming a measure

Whitewater100 — Sun, 28 Aug 2022 14:11:27 GMT

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

Re: Suming a measure

Whitewater100 — Sun, 28 Aug 2022 14:16:50 GMT

Hi:

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

Re: Suming a measure

Anonymous — Sun, 28 Aug 2022 14:40:23 GMT

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.

Re: Suming a measure

Anonymous — Mon, 29 Aug 2022 21:11:16 GMT

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