Forum Discussion
No CALCULATE Challenge -- Round #2
- 3 years ago
AlexisOlson and All,
I was completely convinced that No CALCULATE solution wasn't possible for such kind of problems. My belief is that filter context manipulation is exclusively CALCULATE branded. Yesterday while handling a DAX issue, AlexisOlson No CALCULATE challenge popped up in my mind. With a probable solution, I decided to try the first thing in the morning and seems it covers all the requirements.
Sales Benchmark Ratio 2 = VAR _BenchmarkSales = SUMX ( ALLSELECTED ( Geography[RegionCountryName] ), SUMX ( CROSSJOIN ( TREATAS ( { "North America" }, Geography[ContinentName] ), TREATAS ( { "Store" }, Channel[ChannelName] ) ), [Sales Amount] ) ) VAR _Ratio = DIVIDE ( [Sales Amount], _BenchmarkSales ) RETURN _Ratio
AlexisOlson and All,
I was completely convinced that No CALCULATE solution wasn't possible for such kind of problems. My belief is that filter context manipulation is exclusively CALCULATE branded. Yesterday while handling a DAX issue, AlexisOlson No CALCULATE challenge popped up in my mind. With a probable solution, I decided to try the first thing in the morning and seems it covers all the requirements.
Sales Benchmark Ratio 2 =
VAR _BenchmarkSales =
SUMX (
ALLSELECTED ( Geography[RegionCountryName] ),
SUMX (
CROSSJOIN (
TREATAS ( { "North America" }, Geography[ContinentName] ),
TREATAS ( { "Store" }, Channel[ChannelName] )
),
[Sales Amount]
)
)
VAR _Ratio =
DIVIDE ( [Sales Amount], _BenchmarkSales )
RETURN
_Ratiotamerj1 Brilliant! Nice one Tamer. Going to add that one to my pattern of No CALCULATE tricks.
- tamerj13 years ago
Community Champion
Shall we expect a new one of your brilliant videos? Tag me if you do 😉