margin by month or year

Question

margin by month or year

girl_bj

SSCommitted

Points: 1731
More actions
January 31, 2019 at 6:52 pm

#373658

i would like to count margin by category.
i have sum and group the category amount.
how can i count the margin if i want to have option too see by month or by year.
Country Year Month Total
Australia 2018 December 80000
Australia 2018 February 50000
China 2018 March 20000
India 2018 March 40000

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

girl_bj SSCommitted Points: 1731 More actions · Answer 1

girl_bj - Thursday, January 31, 2019 6:52 PM
i would like to count margin by category.
i have sum and group the category amount.
how can i count the margin if i want to have option too see by month or by year.
Country Year Month Total
Australia 2018 December 80000
Australia 2018 February 50000
China 2018 March 20000
India 2018 March 40000

this is my current query, is my calculation right here?

select year,month,country,total,[$]
,[$] / totalAll as [%]
from(

select year,month,country,total,totalAll
,totalAll-total as [$]
from(

select x.year,x.month,country,sum(total)total
from tableA x
inner join (select year,month,sum(total)totalAll from tableA y where y.year=year and y.month=month group by year,month)zz
on x.year=zz.year and x.month=zz.month
group by x.year,x.month,country

)a
)b

pietlinden SSC Guru Points: 63506 More actions · Answer 2

girl_bj - Thursday, January 31, 2019 6:52 PM
i would like to count margin by category.
i have sum and group the category amount.
how can i count the margin if i want to have option too see by month or by year.
Country Year Month Total
Australia 2018 December 80000
Australia 2018 February 50000
China 2018 March 20000
India 2018 March 40000

What do you mean by "margin"? I'm confused. Margin is usually ([Cost] - [Price]) * [UnitsSold], right? Are you doing this in SQL Server? Because if you're doing this in DAX/Excel, then the answer is different.

girl_bj SSCommitted Points: 1731 More actions · Answer 3

pietlinden - Thursday, January 31, 2019 8:29 PM
girl_bj - Thursday, January 31, 2019 6:52 PM
i would like to count margin by category.
i have sum and group the category amount.
how can i count the margin if i want to have option too see by month or by year.
Country Year Month Total
Australia 2018 December 80000
Australia 2018 February 50000
China 2018 March 20000
India 2018 March 40000
What do you mean by "margin"? I'm confused. Margin is usually ([Cost] - [Price]) * [UnitsSold], right? Are you doing this in SQL Server? Because if you're doing this in DAX/Excel, then the answer is different.

Yes, im doing it in SQL.

pietlinden SSC Guru Points: 63506 More actions · Answer 4

Okay, Can you post some sample data and expected output? I'm still not clear on what you're trying to do.

girl_bj SSCommitted Points: 1731 More actions · Answer 5

pietlinden - Thursday, January 31, 2019 8:56 PM
Okay, Can you post some sample data and expected output? I'm still not clear on what you're trying to do.

this is my current query, is my calculation right here? I would like to get the margin. Not so sure on the calculation.

select year,month,country,total,totalAll,[$]
,[$] / totalAll as [%]
from(
select year,month,country,total,totalAll
,totalAll-total as [$]
from(
select x.year,x.month,country,sum(total)total,totalAll
from #tableA x
inner join (select year,month,sum(total)totalAll from #tableA y where y.year=year and y.month=month group by year,month)zz
on x.year=zz.year and x.month=zz.month
group by x.year,x.month,country,totalAll
)a)b

pietlinden SSC Guru Points: 63506 More actions · Answer 6

When I ran your query, it said it couldn't find the table...
And you never defined what you mean by Margin. Without TotalCost, how do you define Margin?

girl_bj SSCommitted Points: 1731 More actions · Answer 7

pietlinden - Thursday, January 31, 2019 9:12 PM
When I ran your query, it said it couldn't find the table...
And you never defined what you mean by Margin. Without TotalCost, how do you define Margin?

I push it to temp table.
The totalcost I sum it by month. Is that right?

pietlinden SSC Guru Points: 63506 More actions · Answer 8

Since you never really explained what you're trying to accomplish, I have no idea.

If you want an answer, please read this article and follow the directions :Forum Etiquette: How to post data/code on a forum to get the best help