Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

 

 

 

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

 

thanks 

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi Anonymous,

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

Best Regards,
Angelia

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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.