Please help: Deeper Sum Formula

I have a big table, data is:

-----------------------------------------

Date City Store Product Price

Jan 01 Toronto A TV1 500

Jan 01 Toronto B TV2 550

Jan 01 Ottawa C Laptop1 600

Jan 01 Ottawa D Laptop2 650

Jan 02 Toronto A TV1 499

Jan 02 Toronto B TV2 549

Jan 02 Ottawa C Laptop1 599

Jan 02 Ottawa D Laptop2 649

Jan 03 Toronto A TV1 498

Jan 03 Toronto B TV2 548

Jan 03 Ottawa C Laptop1 598

Jan 03 Ottawa D Laptop2 648

---------------------------------------------------

Price changes every day.

I have a date filter, set date = 'Jan 01', I want to display:

-----------------------------------------------------------------

City Store Product Price Deno

Toronto A TV1 500 1050

Toronto B TV2 550 1050

Ottawa C Laptop1 600 1250

Ottawa D Laptop2 650 1250

---------------------------------------------------

How to get Deno? ( sum of prices in a city, two filters: city, date)

The following picture

I need a column shows 2300 in the first row, then 5 rows (City Ottawa) show 1250,

5 rows show 1050.

Using ALLEXCEPT doesn't work because there are two filters: city, date.

Please help me.

amitchandak
2 years ago
Anonymous ,

One is

Total Price = Sum(Table[Price])

another is

Total Price City = calculate(Sum(Table[Price]), removefilters(Table[Store]) )

or

Total Price City = calculate(Sum(Table[Price]), filter(allselected(Table),Table[Store] = max(Table[Store]) ) )

Percent of Total and Percent of SubTotal https://www.youtube.com/watch?v=6jTildcV2ho
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s
Anonymous
2 years ago
I use
Deno = Calculate(SUM('Table'[Price]), ALLEXCEPT('Table', 'Table'[Date], 'Table'[City]))
to get the following picture:

Forum Discussion