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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
leolapa_br
Resolver I
Resolver I

Summary table from VAR temporary table

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

VAR_Table.png

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:

Summ_Table.png

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!

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

 

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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