Forum Discussion

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago
Solved

No CALCULATE Challenge -- Round #2

I learned a lot from the brilliant folks who participated in my previous challenge, so I wanted to explore another situation where I find CALCULATE highly useful and see how others manage to do witho...
  • tamerj1's avatar
    tamerj1
    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
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
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's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    3 years ago

    tamerj1 Nice work! This does appear to satisfy the requirements, so I'll accept it. The trick here is using the implicit CALCULATE context transition that comes from referencing another measure, [Sales Amount].

    For an extra challenge, can anyone modify this so that it works as a standalone measure? Maybe using SUMMARIZE since that can also do a context transition.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      AlexisOlson 
      Yes sure, I thought about that already but it does not look so nice

      Sales Benchmark Ratio 2 = 
VAR _BenchmarkSales =
    SUMX (
        SUMMARIZE ( 
            CROSSJOIN ( 
                ALLSELECTED ( Geography[RegionCountryName] ),
                CROSSJOIN (
                    TREATAS ( { "North America" }, Geography[ContinentName] ),
                    TREATAS ( { "Store" }, Channel[ChannelName] )
                )
            ),
            Geography[RegionCountryName],
            Geography[ContinentName],
            Channel[ChannelName],
            "@Amt", SUM ( Sales[SalesAmount] )
        ),
    [@Amt]
    )
VAR _Ratio =
    DIVIDE ( [Sales Amount], _BenchmarkSales )
RETURN
    _Ratio
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    tamerj1 Brilliant! Nice one Tamer. Going to add that one to my pattern of No CALCULATE tricks.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Greg_Deckler 

      Shall we expect a new one of your brilliant videos? Tag me if you do 😉