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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vjnvinod
Impactful Individual
Impactful Individual

groupby

Hi,

 

I wanted to create a new table, where my expected result should have been like  the below

 

Account SectorTERFYTD USD
media & entertainment2000
Technology3000
Telecom4000

 

 

But i am using the below DAX and my results are weird, its not adding up the values from coloumn TER_FYTD_USD (see below table)

new table = GROUPBY(Inbound,Inbound[AccountSector],(Inbound[TER_FYTD_USD]))

 

Account SectorTERFYTD USD
media & entertainment500
media & entertainment500
media & entertainment1000
Technology1000
Technology1000
Technology1000
Telecom2000
Telecom1000
Telecom1000

 

 

8 REPLIES 8
Anonymous
Not applicable

table =
SUMMARIZE ( Inbound[AccountSector], "Total", SUM ( Inbound[TER_FYTD_USD] ) ) 
Anonymous
Not applicable

Hi @vjnvinod,

 

Use this formula to create your new DAX Table:

NewColumn =
GROUPBY (
Inbound;
Inbound[AccountSector];
"TERFYTD USD"; SUMX ( CURRENTGROUP (); Inbound[TER_FYTD_USD] )
)

Regards.

Hey,

 

as @Anonymous already presented a correct answer using GROUPBY(), I just want to add some additional information about this function https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/ and here the official documentation https://msdn.microsoft.com/en-us/query-bi/dax/groupby-function-dax

 

But if you do not need GROUPBY you should consider to use SUMMARIZECOLUMNS() instead https://msdn.microsoft.com/en-us/query-bi/dax/summarizecolumns-function-dax) or still SUMMARIZE, because it's much more performant.

The DAX statement using SUMMARIZECOLUMNS:

Table using SUMMARIZECOLUMNS = 
SUMMARIZECOLUMNS( 
   'Inbound'[Account Sector],"new column", SUM('Inbound'[TERFYTD USD])
)
 

The perfomance aspect is described in a new DAX book that you will find here: https://www.amazon.com/Beginning-DAX-Power-BI-Intelligence/dp/1484234766/ref=sr_1_1?ie=UTF8&qid=1526070538&sr=8-1&keywords=beginning+dax+with+power+bi by me fellow datanaut @Phil_Seamark

 

Here is the DAX statement using ADDCOLUMNS(SUMMARIZE(...

Table using ADDCOLUMN SUMMARIZE = 
ADDCOLUMNS(
    SUMMARIZE(
        'Inbound'
        ,'Inbound'[Account Sector]
    )
    ,"Wrong Result", SUM('Inbound'[TERFYTD USD])
    ,"Correct Result", CALCULATE(SUM('Inbound'[TERFYTD USD]))
) 

There is also a great article on sqlbi.com coparing SUMMARIZE and SUMMARIZECOLUMNS: https://www.sqlbi.com/articles/introducing-summarizecolumns/

 

Hope this gets you started

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
vjnvinod
Impactful Individual
Impactful Individual

@TomMartens

 

This is really helpful, thanks for sharing this information.

Despite creating this new table and relationship, my data doesn't interacts with each other, not sure why.

Hey,

 

wondering what you mean by "doesn't" interact.

 

I guess this means that there is more than table involved and/or there are slicers.

 

From your post I deduct that you try to "relate" the tables, your original one and the one created by one of the DAX statements. This won't work, due to the design of the Power BI Model. 

 

Ideally you create a pbix with sample data that helps to recreate your issue, upload this pbix file to onedrive or dropbox and share the link.

 

And it would also be helpful to describe in more detail what doesn't work.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
vjnvinod
Impactful Individual
Impactful Individual

 

@TomMartens

 

Hi Tom,

 

sample small dataset

https://www.dropbox.com/s/87mgmsb7u70jloe/Testcheckdale.pbix?dl=0


Expectedresult.PNG

when the regions are filtered at "all" level, the interaction works perfectly fine (see above snapshot) , which is what my expected result should be (that means my outbound is interacting with inbound, pie and the table)

 
Now whats happening is when i filter for any region let say Northeast and then click on other outbound americas, i am not getting the above model results (refer below screenshot), (highligting in red means, they are not interacting with Outbound values) & the numbers of Pie and matrix are not in sync(i am fine with inbound, it may match or maynot)

 

unexpectedresults.PNG

 

let me know, why my model is not working?

Hey,

 

thanks for creating the sample data, but I have to admit I can't detect how the issue that you describe relates to your initial question creating a new table, representing a summariaztion of an existing tabel.

 

Sorry, but now I have to take some rest.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
vjnvinod
Impactful Individual
Impactful Individual

@TomMartens

 

I did tried creating some middle tables, but thats not helping.

Do you have any solution to suggest for my problem?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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