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
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi AlexisOlson ,

 

Could you tell me if your issue has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your issue or share me with your pbix file without sensitive data.


Best Regards,

Rico Zhou

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

There is no qualifying solution so far, so I can't mark it as solved. The calculation can obviously be reproduced without CALCULATE/CALCULATETABLE but no such solution has been robust in the way I specified as the critical aspect of the challenge.

 

I attached a .pbix in the original post along with details.