topic Re: Summary table from VAR temporary table in DAX Commands and Tips

Summary table from VAR temporary table

leolapa_br — Fri, 30 Aug 2024 18:41:35 GMT

I have produced the following VAR (temporary) table called 'Tabela_CP_DiasxValor_DiasPeso_Dias' with 300+ rows:

What I need is to come up with another VAR table that basically summarizes the vendors column highlighted to the left and group them so I get on a second column the total of "@Dias" highlighted to the right for each vendor.

I tried doing that with the following code:

VAR Tabela_CP_DiasporForn = SUMMARIZE( Tabela_CP_DiasxValor_DiasPeso_Dias, [Código - Nome do fornecedor], "@DiasSoma", SUMX(Tabela_CP_DiasxValor_DiasPeso_Dias, [@Dias]) )

But what I end up getting is the summarized table (100+ rows, which is correct) but with the overall total of "@Dias" instead of the total by row/vendor:

I tried going with SUM instead of SUMX but DAX won't take the [@Days] column since it comes from the previous VAR table, and CALCULATE produces the same outcome.

How can I get that done? Any help is appreciated!

Re: Summary table from VAR temporary table

Greg_Deckler — Fri, 30 Aug 2024 19:17:25 GMT

leolapa_br Use GROUPBY with SUMX( CURRENTGROUP(), [@Days] )

Re: Summary table from VAR temporary table

AlexisOlson — Fri, 30 Aug 2024 19:43:24 GMT

GROUPBY is cleaner but another option would be to filter Tabela inside the SUMX.

VAR Tabela_CP_DiasporForn = ADDCOLUMNS ( SUMMARIZE ( Tabela_CP_DiasxValor_DiasPeso_Dias, [Código - Nome do fornecedor] ), "@DiasSoma", VAR _Codigo = [Código - Nome do fornecedor] RETURN SUMX ( FILTER ( Tabela_CP_DiasxValor_DiasPeso_Dias, [Código - Nome do fornecedor] = _Codigo ), [@Dias] ) )