Forum Discussion
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.
thanks
Hi Anonymous,
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.
Please download the file for more details.
https://1drv.ms/u/s!Av62M_jYbMcegRTElJ2bPluGdYGP
Best Regards,
Angelia
6 Replies
Hi Anonymous,
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.
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,
AngeliaHi 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] )
)As you can see it is giving the overall total not just to the sum for that DDID.
thanks
Hi Anonymous,
Could you please share your .pbix file for further analysis?
Best Regards,
Angelia