Forum Discussion

Amit2019's avatar
Amit2019
Frequent Visitor
6 years ago

Groupby Vs Summarize

Hi,
I have below data to calculate "total" by Country Name and City Name

 

I ran the below expression

 

"SUMMARIZE(NATURALINNERJOIN(NATURALINNERJOIN(SELECTCOLUMNS(Country,"CountryID",Country[CountryId]&" ","CountryName",Country[CountryName]),SELECTCOLUMNS(city,"cityID",City[CityId]&" ","CityName",City[CityName],"CountryID",City[CountryId]&" ")),SELECTCOLUMNS(sales,"cityID",Sales[CityId]&" ","Quantity",Sales[Quantity],"Price",Sales[Price])),[CountryName],[CityName],"total",SUMX(sales, Sales[Quantity]))"

 

Below is the result -

 

I ran the below expression

"GROUPBY(NATURALINNERJOIN(NATURALINNERJOIN(SELECTCOLUMNS(Country,"CountryID",Country[CountryId]&" ","CountryName",Country[CountryName]),SELECTCOLUMNS(city,"cityID",City[CityId]&" ","CityName",City[CityName],"CountryID",City[CountryId]&" ")),SELECTCOLUMNS(sales,"cityID",Sales[CityId]&" ","Quantity",Sales[Quantity],"Price",Sales[Price])),[CountryName],[CityName],"total",SUMX(CURRENTGROUP(),[Quantity]))"

 

This time "total" is different-

 

Could anyone please help why there is different "Total".

 

Thanks,

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Amit2019 ,

     

    In my opinion, your second expression is calculating the sum of quantity after the original table is grouped, as table below:

    Please modify the end of second expression as "SUMX(sales, Sales[Quantity]))" and see if it work.

    If not, kindly share your sample data to me if you don't have any Confidential Information. 

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.