How to filter my table visual object by a measure in my table?

[Anonymous]

Hi everyone,

Im having a problem to sum a measure so i want to try to filter my table (visual object) by a measure who is in the table and then try to do a sum operation.

First two columns are not measures. Other are all measures. In the table below all are measures.

I want to sum "Multiplicando" column but i dont know why im getting always the result equals to 0,25 althought the result should be 1,31 that you get if you sum manually.

My last three columns are tries that i did to sum "Multiplicando" column,

My tries are: 

measureta = sumx(Combinar1,[Multiplicando])

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

"Multiplicando" column (the measure that i want to sum) is the next formula : 

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

I hope someone can help me.

[Anonymous]

Hi @Anonymous ,

From the previous discussion, we can only guess that the problem is probably caused by the context.

Could you please provide your desensitized .pbix file?  it can help me to provide the correct solution for you .

Thanks for your efforts & time in advance

Best regards,
Community Support Team_ Binbin Yu

[Anonymous]

Hi,

Here is my pbi:

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

"Tabla principal mejorada" is the important sheet.

ty for helping me.

[johnt75]

Try

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

[Anonymous]

Ty vry much for your reply, the result still being 0,25.

I dont know if this filters has to be in the formula?

ty for helping me

[johnt75]

Take a copy of the table visual and remove all the measures except Multiplicando and Sumando Multipl, so that there are only base columns and these 2 measures.

Use Performance Analyzer to get the DAX code generated for the table and that will show which filters are being applied. That will help us to work out what is going on.

[Anonymous]

Hi,

I left the following columns:

Here is the code that i got using dax analizer:

{"version":"1.1.0","events":[{"name":"User Action","component":"Report Canvas","start":"2022-08-28T00:52:52.899Z","id":"bd2dc7aa640dbd890aa3","metrics":{"sourceLabel":"UserAction_StartedMonitoring"}},{"name":"User Action","component":"Report Canvas","start":"2022-08-28T00:52:54.552Z","id":"b7bb3a737379ab81486e","metrics":{"sourceLabel":"UserAction_Refresh"}},{"name":"Visual Container Lifecycle","component":"Report Canvas","start":"2022-08-28T00:52:54.564Z","end":"2022-08-28T00:52:54.658Z","id":"92f8fc40441e1b07e34a-ebf9598c0d1b7382db65","metrics":{"status":1,"visualTitle":"Segmentación de datos","visualId":"92f8fc40441e1b07e34a","visualType":"slicer","initialLoad":false}},{"name":"Query","component":"Report Canvas","start":"2022-08-28T00:52:54.565Z","end":"2022-08-28T00:52:54.604Z","id":"44e5ade3b2dbdb41c6a1","parentId":"92f8fc40441e1b07e34a-ebf9598c0d1b7382db65"},{"name":"Render","component":"Report Canvas","start":"2022-08-28T00:52:54.603Z","end":"2022-08-28T00:52:54.658Z","id":"9e249855d409a69930e4","parentId":"92f8fc40441e1b07e34a-ebf9598c0d1b7382db65"},{"name":"Data View Transform","component":"Report Canvas","start":"2022-08-28T00:52:54.614Z","end":"2022-08-28T00:52:54.615Z","id":"67a29f32aa51e222d681","parentId":"9e249855d409a69930e4"},{"name":"Query Generation","component":"Report Canvas","start":"2022-08-28T00:52:54.588Z","end":"2022-08-28T00:52:54.592Z","id":"b30d3943ed406b7201ea","parentId":"44e5ade3b2dbdb41c6a1"},{"name":"Visual Container Lifecycle","component":"Report Canvas","start":"2022-08-28T00:52:54.566Z","end":"2022-08-28T00:52:54.659Z","id":"6cc42e805b85b07d6906-b05acf28dd52ea55e00c","metrics":{"status":1,"visualTitle":"Segmentación de datos","visualId":"6cc42e805b85b07d6906","visualType":"slicer","initialLoad":false}},{"name":"Query","component":"Report Canvas","start":"2022-08-28T00:52:54.566Z","end":"2022-08-28T00:52:54.605Z","id":"7b9b94f08e17dba01a8d","parentId":"6cc42e805b85b07d6906-b05acf28dd52ea55e00c"},{"name":"Render","component":"Report Canvas","start":"2022-08-28T00:52:54.604Z","end":"2022-08-28T00:52:54.659Z","id":"608f9b0754855a0640e2","parentId":"6cc42e805b85b07d6906-b05acf28dd52ea55e00c"},{"name":"Data View Transform","component":"Report Canvas","start":"2022-08-28T00:52:54.625Z","end":"2022-08-28T00:52:54.625Z","id":"8f928124018a1974ad05","parentId":"608f9b0754855a0640e2"},{"name":"Query Generation","component":"Report Canvas","start":"2022-08-28T00:52:54.593Z","end":"2022-08-28T00:52:54.594Z","id":"5e20144fa2bb3d01d504","parentId":"7b9b94f08e17dba01a8d"},{"name":"Visual Container Lifecycle","component":"Report Canvas","start":"2022-08-28T00:52:54.567Z","end":"2022-08-28T00:52:54.659Z","id":"99e621f0bdbd03bd0272-e6fa3c72c4a570bc7979","metrics":{"status":1,"visualTitle":"Segmentación de datos","visualId":"99e621f0bdbd03bd0272","visualType":"slicer","initialLoad":false}},{"name":"Query","component":"Report Canvas","start":"2022-08-28T00:52:54.568Z","end":"2022-08-28T00:52:54.605Z","id":"f34d8686169ae27db040","parentId":"99e621f0bdbd03bd0272-e6fa3c72c4a570bc7979"},{"name":"Render","component":"Report Canvas","start":"2022-08-28T00:52:54.605Z","end":"2022-08-28T00:52:54.659Z","id":"691ad5238bd9d47a4b33","parentId":"99e621f0bdbd03bd0272-e6fa3c72c4a570bc7979"},{"name":"Data View Transform","component":"Report Canvas","start":"2022-08-28T00:52:54.632Z","end":"2022-08-28T00:52:54.633Z","id":"51e91f6091436096c491","parentId":"691ad5238bd9d47a4b33"},{"name":"Query Generation","component":"Report Canvas","start":"2022-08-28T00:52:54.595Z","end":"2022-08-28T00:52:54.596Z","id":"9e4fa49f9c90800a1e5e","parentId":"f34d8686169ae27db040"},{"name":"Visual Container Lifecycle","component":"Report Canvas","start":"2022-08-28T00:52:54.568Z","end":"2022-08-28T00:52:54.659Z","id":"f9a41951c8077eceb9ea-80638927dce3ad1aa86a","metrics":{"status":1,"visualTitle":"Segmentación de datos","visualId":"f9a41951c8077eceb9ea","visualType":"slicer","initialLoad":false}},{"name":"Query","component":"Report Canvas","start":"2022-08-28T00:52:54.569Z","end":"2022-08-28T00:52:54.606Z","id":"65c177bcc8eb0ca05a47","parentId":"f9a41951c8077eceb9ea-80638927dce3ad1aa86a"},{"name":"Render","component":"Report Canvas","start":"2022-08-28T00:52:54.605Z","end":"2022-08-28T00:52:54.659Z","id":"40710571306b47677a1a","parentId":"f9a41951c8077eceb9ea-80638927dce3ad1aa86a"},{"name":"Data View Transform","component":"Report Canvas","start":"2022-08-28T00:52:54.641Z","end":"2022-08-28T00:52:54.641Z","id":"61a14106091c9bbc0751","parentId":"40710571306b47677a1a"},{"name":"Query Generation","component":"Report Canvas","start":"2022-08-28T00:52:54.597Z","end":"2022-08-28T00:52:54.598Z","id":"01144db385c7c2a32011","parentId":"65c177bcc8eb0ca05a47"},{"name":"Visual Container Lifecycle","component":"Report Canvas","start":"2022-08-28T00:52:54.569Z","end":"2022-08-28T00:52:54.834Z","id":"7d7217d5e82e62746130-fa7be107db1a06494086","metrics":{"status":1,"visualTitle":"Tarjeta","visualId":"7d7217d5e82e62746130","visualType":"card","initialLoad":false}},{"name":"Query","component":"Report Canvas","start":"2022-08-28T00:52:54.570Z","end":"2022-08-28T00:52:54.821Z","id":"7bd4a20281868e82108d","parentId":"7d7217d5e82e62746130-fa7be107db1a06494086"},{"name":"Render","component":"Report Canvas","start":"2022-08-28T00:52:54.821Z","end":"2022-08-28T00:52:54.834Z","id":"f970e5d8720000eeebd7","parentId":"7d7217d5e82e62746130-fa7be107db1a06494086"},{"name":"Data View Transform","component":"Report Canvas","start":"2022-08-28T00:52:54.823Z","end":"2022-08-28T00:52:54.824Z","id":"d8bd6eb2a6c11e44cdc1","parentId":"f970e5d8720000eeebd7"},{"name":"Execute Semantic Query","component":"DSE","start":"2022-08-28T00:52:54.738Z","end":"2022-08-28T00:52:54.756Z","id":"a3bc3d58-1dbe-48d7-a07a-aa639d4ad39c","parentId":"7bd4a20281868e82108d"},{"name":"Query Generation","component":"Report Canvas","start":"2022-08-28T00:52:54.598Z","end":"2022-08-28T00:52:54.600Z","id":"1542db52c0254dd03284","parentId":"7bd4a20281868e82108d"},{"name":"Query Pending","component":"Report Canvas","start":"2022-08-28T00:52:54.745Z","end":"2022-08-28T00:52:54.776Z","id":"06f08073c1ada8ed2e21","parentId":"7bd4a20281868e82108d"},{"name":"Parse Query Result","component":"Report Canvas","start":"2022-08-28T00:52:54.819Z","end":"2022-08-28T00:52:54.820Z","id":"134f3296d46270a64880","parentId":"7bd4a20281868e82108d"},{"name":"Execute DAX Query","component":"DSE","start":"2022-08-28T00:52:54.744Z","end":"2022-08-28T00:52:54.755Z","id":"f52e857f-3b0e-421c-bede-ef93e60663d6","parentId":"a3bc3d58-1dbe-48d7-a07a-aa639d4ad39c","metrics":{"QueryText":"DEFINE\r\n VAR __DS0FilterTable = \r\n TREATAS({\"7702121001739\"}, 'Combinar1'[upc])\r\n\r\n VAR __DS0FilterTable2 = \r\n TREATAS({\"ARIZTIA\"}, 'Combinar1'[fabricante])\r\n\r\nEVALUATE\r\n SUMMARIZECOLUMNS(\r\n __DS0FilterTable,\r\n __DS0FilterTable2,\r\n \"Mindescri_item\", IGNORE(CALCULATE(MIN('Combinar1'[descri_item])))\r\n )","RowCount":1}},{"name":"Execute Query","component":"AS","start":"2022-08-28T00:52:54.750Z","end":"2022-08-28T00:52:54.753Z","id":"EB77D104-3AB7-4730-B6B6-3F8A7B28B5A7","parentId":"f52e857f-3b0e-421c-bede-ef93e60663d6"},{"name":"Serialize Rowset","component":"AS","start":"2022-08-28T00:52:54.753Z","end":"2022-08-28T00:52:54.753Z","id":"1C476E2C-F5A5-425F-839C-46BBA28186DE","parentId":"EB77D104-3AB7-4730-B6B6-3F8A7B28B5A7"},{"name":"Visual Container Lifecycle","component":"Report Canvas","start":"2022-08-28T00:52:54.571Z","end":"2022-08-28T00:53:00.015Z","id":"1caca64e0610038ec832-91a46d1400304c373d09","metrics":{"status":1,"visualTitle":"Tarjeta","visualId":"1caca64e0610038ec832","visualType":"card","initialLoad":false}},{"name":"Query","component":"Report Canvas","start":"2022-08-28T00:52:54.571Z","end":"2022-08-28T00:53:00.008Z","id":"9999584c7508003064eb","parentId":"1caca64e0610038ec832-91a46d1400304c373d09"},{"name":"Render","component":"Report Canvas","start":"2022-08-28T00:53:00.008Z","end":"2022-08-28T00:53:00.015Z","id":"c8fc0435789b28ce0723","parentId":"1caca64e0610038ec832-91a46d1400304c373d09"},{"name":"Data View Transform","component":"Report Canvas","start":"2022-08-28T00:53:00.009Z","end":"2022-08-28T00:53:00.010Z","id":"58a0be44b09e60206198","parentId":"c8fc0435789b28ce0723"},{"name":"Execute Semantic Query","component":"DSE","start":"2022-08-28T00:52:54.738Z","end":"2022-08-28T00:52:59.965Z","id":"863df076-9241-43b7-ba33-7fdac202c9f5","parentId":"9999584c7508003064eb"},{"name":"Query Generation","component":"Report Canvas","start":"2022-08-28T00:52:54.601Z","end":"2022-08-28T00:52:54.602Z","id":"31686574eca6b1048792","parentId":"9999584c7508003064eb"},{"name":"Query Pending","component":"Report Canvas","start":"2022-08-28T00:52:54.746Z","end":"2022-08-28T00:52:54.777Z","id":"442878f66491eb007b10","parentId":"9999584c7508003064eb"},{"name":"Parse Query Result","component":"Report Canvas","start":"2022-08-28T00:53:00.007Z","end":"2022-08-28T00:53:00.007Z","id":"6db50649140eb1839c0c","parentId":"9999584c7508003064eb"},{"name":"Open Connection","component":"DSE","start":"2022-08-28T00:52:54.744Z","end":"2022-08-28T00:52:54.761Z","id":"4778f60f-de85-45a0-b34c-8412801dc83f","parentId":"863df076-9241-43b7-ba33-7fdac202c9f5"},{"name":"Execute DAX Query","component":"DSE","start":"2022-08-28T00:52:54.761Z","end":"2022-08-28T00:52:59.964Z","id":"3236ea13-3b13-4bde-9271-283fce2bb5d4","parentId":"863df076-9241-43b7-ba33-7fdac202c9f5","metrics":{"QueryText":"DEFINE\r\n VAR __DS0FilterTable = \r\n TREATAS({\"7702121001739\"}, 'Combinar1'[upc])\r\n\r\n VAR __DS0FilterTable2 = \r\n TREATAS({\"ARIZTIA\"}, 'Combinar1'[fabricante])\r\n\r\nEVALUATE\r\n SUMMARIZECOLUMNS(\r\n __DS0FilterTable,\r\n __DS0FilterTable2,\r\n \"Final_Elasticidad_Material\", IGNORE('Medidas'[Final Elasticidad Material])\r\n )","RowCount":1}},{"name":"Execute Query","component":"AS","start":"2022-08-28T00:52:54.763Z","end":"2022-08-28T00:52:59.957Z","id":"7C8F2B91-A543-490C-979B-BC4150E00135","parentId":"3236ea13-3b13-4bde-9271-283fce2bb5d4"},{"name":"Serialize Rowset","component":"AS","start":"2022-08-28T00:52:59.957Z","end":"2022-08-28T00:52:59.957Z","id":"21008C96-5401-4B26-8B11-F0405FA9F892","parentId":"7C8F2B91-A543-490C-979B-BC4150E00135"},{"name":"Visual Container Lifecycle","component":"Report Canvas","start":"2022-08-28T00:52:54.667Z","end":"2022-08-28T00:52:55.206Z","id":"001d46d2c2800e6c6a6a-5fdda65d5b96309e87cd","metrics":{"status":1,"visualTitle":"Tabla","visualId":"001d46d2c2800e6c6a6a","visualType":"tableEx","initialLoad":false}},{"name":"Query","component":"Report Canvas","start":"2022-08-28T00:52:54.668Z","end":"2022-08-28T00:52:55.132Z","id":"a217f4170730232385e6","parentId":"001d46d2c2800e6c6a6a-5fdda65d5b96309e87cd"},{"name":"Render","component":"Report Canvas","start":"2022-08-28T00:52:55.132Z","end":"2022-08-28T00:52:55.206Z","id":"8e74a38acca68113e0e5","parentId":"001d46d2c2800e6c6a6a-5fdda65d5b96309e87cd"},{"name":"Data View Transform","component":"Report Canvas","start":"2022-08-28T00:52:55.134Z","end":"2022-08-28T00:52:55.136Z","id":"a06a9a4fe5e1cae2c9d5","parentId":"8e74a38acca68113e0e5"},{"name":"Execute Semantic Query","component":"DSE","start":"2022-08-28T00:52:54.743Z","end":"2022-08-28T00:52:55.065Z","id":"c435088b-6fba-45a1-8246-2ace0f21eb5d","parentId":"a217f4170730232385e6"},{"name":"Query Generation","component":"Report Canvas","start":"2022-08-28T00:52:54.690Z","end":"2022-08-28T00:52:54.692Z","id":"32b398510d74b0decb13","parentId":"a217f4170730232385e6"},{"name":"Query Pending","component":"Report Canvas","start":"2022-08-28T00:52:54.747Z","end":"2022-08-28T00:52:54.778Z","id":"ca84646d5a6272ad90ce","parentId":"a217f4170730232385e6"},{"name":"Parse Query Result","component":"Report Canvas","start":"2022-08-28T00:52:55.125Z","end":"2022-08-28T00:52:55.130Z","id":"32236e342e30c5b77007","parentId":"a217f4170730232385e6"},{"name":"Open Connection","component":"DSE","start":"2022-08-28T00:52:54.747Z","end":"2022-08-28T00:52:54.761Z","id":"61611fa6-0360-42e6-94d2-7271de51171c","parentId":"c435088b-6fba-45a1-8246-2ace0f21eb5d"},{"name":"Execute DAX Query","component":"DSE","start":"2022-08-28T00:52:54.761Z","end":"2022-08-28T00:52:55.062Z","id":"fad3d670-287e-43c1-a50a-93fc4e56a445","parentId":"c435088b-6fba-45a1-8246-2ace0f21eb5d","metrics":{"QueryText":"DEFINE\r\n VAR __DS0FilterTable = \r\n TREATAS({\"7702121001739\"}, 'Combinar1'[upc])\r\n\r\n VAR __DS0FilterTable2 = \r\n TREATAS({\"ARIZTIA\"}, 'Combinar1'[fabricante])\r\n\r\n VAR __DS0Core = \r\n SUMMARIZECOLUMNS(\r\n ROLLUPADDISSUBTOTAL(\r\n ROLLUPGROUP('Combinar1'[NumSem], 'Combinar1'[AñoSemOrd]), \"IsGrandTotalRowTotal\"\r\n ),\r\n __DS0FilterTable,\r\n __DS0FilterTable2,\r\n \"Measure_Filter\", 'Medidas'[Measure Filter],\r\n \"Sumando_Variaciones_Porcentuales_PRecio_por_KILO\", 'Medidas'[Sumando Variaciones Porcentuales PRecio por KILO],\r\n \"Promedio_cada_fila_Var___Kilos\", 'Medidas'[Promedio cada fila Var % Kilos],\r\n \"Promedio_cada_fila_Var___Precio_por_Kilos\", 'Medidas'[Promedio cada fila Var % Precio por Kilos],\r\n \"Var_Kilos___Var_Kilos_Promedio\", 'Medidas'[Var Kilos - Var Kilos Promedio],\r\n \"Var_Precio___Var_Precio_Promedio\", 'Medidas'[Var Precio - Var Precio Promedio],\r\n \"Multiplicando\", 'Medidas'[Multiplicando],\r\n \"Sumando_Variaciones_Porcentuales_KILOS\", 'Medidas'[Sumando Variaciones Porcentuales KILOS],\r\n \"Sumando_Multipl\", 'Medidas'[Sumando Multipl]\r\n )\r\n\r\n VAR __DS0PrimaryWindowed = \r\n TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Combinar1'[NumSem], 1, 'Combinar1'[AñoSemOrd], 1)\r\n\r\nEVALUATE\r\n __DS0PrimaryWindowed\r\n\r\nORDER BY\r\n [IsGrandTotalRowTotal] DESC, 'Combinar1'[NumSem], 'Combinar1'[AñoSemOrd]","RowCount":75}},{"name":"Execute Query","component":"AS","start":"2022-08-28T00:52:54.763Z","end":"2022-08-28T00:52:55.037Z","id":"7B1B9EEC-23E0-450C-983F-1FA4F62B14F5","parentId":"fad3d670-287e-43c1-a50a-93fc4e56a445"},{"name":"Serialize Rowset","component":"AS","start":"2022-08-28T00:52:55.037Z","end":"2022-08-28T00:52:55.037Z","id":"F9C2DFBE-83A6-4BFB-8105-C955CD198938","parentId":"7B1B9EEC-23E0-450C-983F-1FA4F62B14F5"}],"sessionId":"00edf541-91ae-4d0a-b994-4000922c9111"}

Ty vry much.