cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Group by Visual table

Hello, I have the below table which I have created based upon quite a few comuns in different tables. We want it to group by ddiD to only show 1 line and sums in-nm and in-nk.

 

Capture.PNG 

 

thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Group by Visual table

Hi @damobrowny,

After research, your [in-nk], [in-nm] column come from another table which relate to your 'dairy' table indirectly. You need to transfer the value to the sample or one table related directly. I create calculated columns [nk1], [nm1], [nk2], [nm2], then use SUMMARIZE to create a new table to get expected result.

1.PNG

Please download the file for more details.

https://1drv.ms/u/s!Av62M_jYbMcegRTElJ2bPluGdYGP

Best Regards,
Angelia

View solution in original post

6 REPLIES 6
Highlighted
Microsoft
Microsoft

Re: Group by Visual table

Hi @damobrowny,

I assume your table name as 'Table1'. You can create a new table by clicking "New Table" under Modeling on Home page, type the formula below. 

New Table =
SUMMARIZE (
    Table1,
    Table1[ddiD],
    "in-nm", SUM ( Table[in-nm] ),
    "in-nk", SUM ( Table[in-nk] )
)


For another option, you can also group by ddiD in Query Editor(button highlighted in red line) as follows, click Advanced->sum of  in-nm and in-nk group by [ddiD] column.

1.PNG

If you use option1, you will get a new table to display the result. The resouce table will be changed as the expected result if you use option2.

Best Regards,
Angelia

Highlighted
Helper II
Helper II

Re: Group by Visual table

Hi @v-huizhn-msft, thank you for the response. Option 1 is probably better as the 2 results are in 2 different tables. Here is the code I entered and the result

 

New Table =
SUMMARIZE (
diary,
diary[ddiD],
"in-nm", SUM ( 'inv-rem'[in-nm] ),
"in-nk", SUM ( 'inv-rem'[in-nk] )
)

 

Capture.PNG 

 

As you can see it is giving the overall total not just to the sum for that DDID.

 

thanks 

Highlighted
Microsoft
Microsoft

Re: Group by Visual table

Hi @damobrowny,

Could you please share your .pbix file for further analysis?

Best Regards,
Angelia

Highlighted
Helper II
Helper II

Re: Group by Visual table

Morning @v-huizhn-msft, thanks for looking at this. File is uploaded here

 

https://www.dropbox.com/s/fzkhijotaa1eweo/Warehouse%20Calendars.pbix?dl=0 

Highlighted
Microsoft
Microsoft

Re: Group by Visual table

Hi @damobrowny,

After research, your [in-nk], [in-nm] column come from another table which relate to your 'dairy' table indirectly. You need to transfer the value to the sample or one table related directly. I create calculated columns [nk1], [nm1], [nk2], [nm2], then use SUMMARIZE to create a new table to get expected result.

1.PNG

Please download the file for more details.

https://1drv.ms/u/s!Av62M_jYbMcegRTElJ2bPluGdYGP

Best Regards,
Angelia

View solution in original post

Highlighted
Helper II
Helper II

Re: Group by Visual table

Hi @v-huizhn-msft, thank you so much that has certainly got us closer. 1 last issue is it now displays an incorrect in-nm and in-nk becuase packs may contain more than 1 item.

 

an example of this is DDid 510880. It is showing the in-nm and in-nk as x3 the amount of the actual vaulues as there are 3 items which share the same pack so it is counting the pack 3 times.

 

The packs on this DDid which contain multiple items are 1384339P and 1384340P. We want to only count that pack weight once.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors