Create a Measure at a higher level that the report. (Level Metric)

[Anonymous]

Need to create a measure that is the sum total at the month level.

I have a sum(column) at the report level, but I need a sum at the exact month (level metric in microstrategy). That total month sales would not be on the report per-se but used to calculate additional measures.

I can't seem to figure out how to achieve this. I found a few postings on this topic, but can't see to find a good answer.

Month	Region	Sales	Total Month Sales
Nov-18	1	20	110
	2	20	110
	3	30	110
	4	40	110
		110

[parry2k]

@Anonymous try this

Total = CALCULATE( SUM( Table1[Qty] ), ALLEXCEPT( Table1, Table1[Month] ) )

[Anonymous]

Probably should have been a little more clear. This is "closer" to what I needed but I need it to "break" by month. But each line to have that "month" total.

Month	Region	Sales	Total
18-Nov	1	20	110
	2	20	110
	3	30	110
	4	40	110
		110
18-Dec	1	30	150
	2	30	150
	3	40	150
	4	50	150
		150

[parry2k]

@Anonymous the measure I provided should work, you need to use matrix visual,

add month, region on rows, sales and new measure I provided on value

- turn on stepped layout on format page

- turn off column total 

- drill down to region level.

[Anonymous]

That was actually still giving me a GRAND TOTAL.

I played around a bit and got this to work. Not sure why?

Total = CALCULATE( SUM(FactTable[Qty] ), ALLEXCEPT( FactTable, LookupTable[Month] ) )

[Anonymous]

Actually, it wasn't 100% correct, but it is closer to what I wanted.