Calculate Sales by Channel by Region by County by Month

[Anonymous]

Hi Experts

I want to calculate the Sale for each period as a value of the total sales by Channel by Region by County by Month. I need a Calculated Column....

Sample Data

Channel	Region	Month	Year	Country	Sales
OOO	EMEA	Jan	2020	Farnce	2227
OOO	EMEA	Feb	2020	Farnce	2054
OOO	EMEA	Mar	2020	Farnce	1830
OOO	EMEA	Apr	2020	Farnce	1292
OOO	EMEA	May	2020	Farnce	2325
OOO	EMEA	Jun	2020	Farnce	1918
OOO	EMEA	Jul	2020	Farnce	1905
OOO	EMEA	Aug	2020	Farnce	1507
OOO	EMEA	Sep	2020	Farnce	2093
OOO	EMEA	Oct	2020	Farnce	2286
OOO	EMEA	Nov	2020	Farnce	2310
OOO	EMEA	Dec	2020	Farnce	1643
					23390

[ryan_mayu]

@Anonymous 

what's your expected output?

[Anonymous]

Using the above sample data. then for Jan 2227/23390 = 0.095212... i want the calculated column measure to into account where we have mulitple regions and channels Per Month 

[Jihwan_Kim]

Hi, @Anonymous 

Please try the below-calculated column.

newcolunm = DIVIDE( 'Table'[Sales],

                                 CALCULATE( SUMX('Table', 'Table'[Sales]), ALL('Table'),

                                                      VALUES('Table'[Channel]),

                                                      VALUES('Table'[Region]),

                                                      VALUES( 'Table'[Year]),

                                                      VALUES('Table'[Country])))

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[ryan_mayu]

@Anonymous 

you can try to create a column like this

Column = 'Table'[Sales]/CALCULATE(sum('Table'[Sales]),ALLEXCEPT('Table','Table'[Channel],'Table'[Region],'Table'[Year],'Table'[Country]))

[Anonymous]

Hi ryan....

Would the above accomdate for the following situation too

[ryan_mayu]

@Anonymous 

Column = CALCULATE(sum('Table'[Sales]),ALLEXCEPT('Table','Table'[Channel],'Table'[Region],'Table'[Year],'Table'[Country],'Table'[Month]))/CALCULATE(sum('Table'[Sales]),ALLEXCEPT('Table','Table'[Channel],'Table'[Region],'Table'[Year],'Table'[Country]))

you can try this

[Anonymous]

Hi Ryan, Thanks. What results do you get in your table - my internet is down at the moment, using mobile to post.

[ryan_mayu · Accepted solution]

@Anonymous 

please kindly check

[Anonymous]

Hi Ryan Part 2 to the question if you are interested?

https://community.powerbi.com/t5/Desktop/Multiple-Sale-Result-by-Second-Table-Values/m-p/1750155#M68...