No CALCULATE Challenge -- Round #2

Interesting challenge 👋 Looking forward to some tricky solutions as I've already spent more than half hour but no luck. I really doubt there exists an elegant, error-proof alternative, especially when some new slicers will intervene.

Off the track comment on CALCULATE

I admit that the idea to kick the ass of CALCUALTION is buzzy at the first glance; but with further comprehension of DAX, I'd like to say the mechanism of context transition triggered by CALCULATE is a genius one! It differentiates DAX from other traditional "context insensitive" lauguages like Excel formulas, SQL etc.

AlexisOlson Technically meets the letter of the law with regard to the challenge. 🙂

Sales Benchmark Ratio NC Cheat = 
VAR _BenchmarkSalesTable =
    CALCULATETABLE(
        'Sales',
        ALL ( Geography ),
        Geography[ContinentName] = "North America",
        Channel[ChannelName] = "Store"
    )
VAR _Ratio = DIVIDE ( [Sales Amount 1], SUMX(_BenchmarkSalesTable,[SalesAmount]) )
RETURN
    _Ratio

AlexisOlson This one works but is slow. Sharing it in case someone has a brilliant idea on how to optimize

Sales Benchmark Ratio NC2 = 
    VAR __Products = DISTINCT('Product'[ProductKey])
    VAR __Promotions = DISTINCT(Promotion[PromotionKey])
    VAR __GeoKeys = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Geography'), [ContinentName] = "North America"), "GeoKey",[GeographyKey]))
    VAR __Stores = DISTINCT(SELECTCOLUMNS(FILTER(ALL(Stores), [GeographyKey] IN __GeoKeys),"StoreKey",[StoreKey]))
    VAR __Channels = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Channel'), [ChannelName] = "Store"),"ChannelKey",[Channel]))
    VAR __BenchmarkSalesTable = 
        FILTER(
            FILTER(
                FILTER(
                    FILTER(
                        ALL('Sales'),
                        [ProductKey] IN __Products
                    ),
                    [PromotionKey] IN __Promotions
                ),
                [StoreKey] IN __Stores
            ),
            [channelKey] IN __Channels
        )
    VAR __BenchmarkSales = SUMX(__BenchmarkSalesTable, [SalesAmount])
    VAR __Ratio = DIVIDE ( [Sales Amount 1], __BenchmarkSales )
RETURN
    __Ratio

Hi AlexisOlson 

Doesn't seem that simple 😅

it's 8:15pm right now here and I don't have access to my laptop. I hope not to close the subject until tomorrow same time. I will try to give it a shot anyway from my phone. 
I just wanted to say that even this CALCULATE version of the measure won't work in all scenarios. For example if you have Country as a slicer or if you have any attribute from the Channel table other than Channel Name in the rows, I guess it won't work. So please be nice and grant a little bit of flexibility 😎

AlexisOlson 

Not the best performance for sure 

Sales Benchmark Ratio =
VAR _BenchmarkSales =
SUMX (
FILTER (
ALLSELECTED ( Sales ),
RELATED ( Geography[ContinentName] ) = "North America"
&& RELATED ( Channel[ChannelName] ) = "Store"
),
Sales[Quantity] * Sales[Price]
)
VAR _Ratio =
DIVIDE ( [Sales Amount], _BenchmarkSales )
RETURN
_Ratio

AlexisOlson - OK, interesting....fair or foul to tinker with the data model and collapse the geography snowflake to simplify?

AlexisOlson Great challenge, but because of the requirement I don't think it is possible to make the solution dynamic, so CALCULATE wins, Yay!

Otherwise with the current status of the report I would write this:

Sales Benchmark Ratio 2 = 
VAR SalesAmount = [Sales Amount]
VAR _BenchmarkSales = 
    FILTER ( 
        ALL ( Sales ),
        RELATED ( Geography[ContinentName] ) = "North America"
            && RELATED ( Channel[ChannelName] ) = "Store"
            && RELATED ( 'Product'[ClassName] ) IN VALUES ( 'Product'[ClassName] )
            && RELATED ( Promotion[DiscountPercent] ) IN VALUES ( Promotion[DiscountPercent] )
    )
VAR Result = 
    DIVIDE ( 
        SalesAmount, 
        SUMX ( _BenchmarkSales, Sales[SalesAmount] ) 
    )
RETURN Result