superfly71 superfly71 - 2 months ago 7
C# Question

Linq sum of percentage of yearly sales of the Others category exceed 100%

I have a linq query that calculates the sum of sales by year and country. Everything works fine except when a put those countries with less than 2% of total sales into the "Others" category

My code:

using (var context = new ChinookContext())
{
var rawset = context.Invoices.Include(l => l.InvoiceLines)
.GroupBy(i => new { i.BillingCountry, i.InvoiceDate.Year })
.Select(f => new
{
Country = f.Key.BillingCountry,
Year = f.Key.Year,
TotalSales = f.Sum(l => l.InvoiceLines.Sum(t => t.Quantity * t.UnitPrice))
})
.ToList();

var result = rawset
.Select(r => new {
GrandTotal = rawset.Sum(t => t.TotalSales),
CountryTotal =
rawset.GroupBy(c => c.Country)
.Select(p => new
{
CountryKey = p.Key,
CountryTotalSales = p.Sum(t => t.TotalSales)
}),
Member = r})
.Select(f => new
{
Country = (
f.CountryTotal
.Where(c => c.CountryKey == f.Member.Country)
.Select(x => x.CountryTotalSales).SingleOrDefault()
/ f.GrandTotal > 0.02M)
? f.Member.Country : "Others",
Year = f.Member.Year,
YearlySales = f.Member.TotalSales,
TotalSalesByCountry = f.CountryTotal
.Where(c => c.CountryKey == f.Member.Country)
.Select(c => c.CountryTotalSales).FirstOrDefault(),
GrandTotal = f.GrandTotal
})
.ToList();

var finalresult = result.GroupBy(r => new { r.Country, r.Year })
.Select(final => new
{
Country = final.Key.Country,
Year = final.Key.Year,
YearlySales = final.Sum(y => y.YearlySales),
PercentageByCountry = final.Sum(y => y.YearlySales)
/ final.Sum(c => c.TotalSalesByCountry)
* 100,
PercentageByGrandTotal = final.Sum(y => y.YearlySales)
/ final.Select(x => x.GrandTotal).FirstOrDefault()
* 100
})
.OrderBy(f => f.Country)
.ThenBy(f => f.Year);
}


The problem is the percentage of yearly sale for each country for the total sales for that country.

All other countries worked well except for the "Others" category which exceeds 100%.

Any advice would be greatly appreciated.

Thanks in advance.

Answer

While calculating TotalSalesByCountry you are not considering "Others" case, ie when its less than 2%. So the calculation will go wrong when you do PercentageByCountry = final.Sum(y => y.YearlySales) / final.Sum(c => c.TotalSalesByCountry) * 100 in the case of "Others" country

Comments