Forum Discussion

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

No CALCULATE Challenge -- Round #2b

tamerj1 provided an ingenious solution to my No CALCULATE Challenge -- Round #2. However, I couldn't easily figure out how to adapt it to a situation where the measure is non-additive and the filteri...
  • lbendlin's avatar
    lbendlin
    2 years ago

     

     

     inside a measure,

     

    Note that measures implicitly use CALCULATE, somewhat negating your premise.

     

    Having said that, try this version

     

     

    DONCB = 
VAR b = ADDCOLUMNS(ALL(Geography[RegionCountryName]),"do", [Distinct Orders])
RETURN SUMX(FILTER(b,[RegionCountryName]<>"China"),[do])

     

     

     

  • tamerj1's avatar
    tamerj1
    2 years ago

    AlexisOlson 
    Here is a solution using SUMMARIZECOLUMNS.

    Distinct Orders (Non-China Benchmark) TJ = 
COUNTROWS (
    SUMMARIZECOLUMNS ( Sales[OrderKey], 
        FILTER ( 
            ALL ( Geography[RegionCountryName] ),
            Geography[RegionCountryName] <> "China" 
        ),
        "@Count", COUNTROWS ( Sales )
    )
)

    However, applying the same filter over a higher cardinality column will reveal the difference with the SUMX solution provided by lbendlin . DISTINCTCOUNT is non-additive calculation, which means SUMX will result in wrong results.

     

     

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

Greg_Deckler, the distinct count for Asia Economy without any visual filter is 33,852. The distinct count for China Economy is 5,663. The difference is 28,189, exactly as intended. I really do want to keep the things filtered out in the visual filter in my real-life measure that motivated this contrived example.

 

Your measure is close but fails on the grand total line.

 

lbendlin, I don't think even the most extreme No CALCULATE proponents would go so far as to ban measures altogether.

 

Your updated measure appears to work as intended so I've accepted it as a solution.

I realize now that I didn't think carefully enough when designing the problem. In my actual application, the measure isn't additive along any dimension, so DISTINCTCOUNT isn't nonadditive enough to solve what I'm really after.

 

This is a bit closer to what I'm trying to solve and might be more intuitive even though it's slightly more complex:

 

Median Markup =
MEDIANX ( Sales, -1 + Sales[SalesAmount] / Sales[TotalCost] )
Markup Non-Deluxe Ratio =
DIVIDE (
    [Median Markup],
    CALCULATE ( [Median Markup], 'Product'[ClassName] <> "Deluxe" )
)

 

Just like in my post, it should be unsurprising that the denominator is the same regardless of the ClassName selected via slicers or visual filters.

 

Consider this Round #2c. Ideally, the solution should be general enough that it works for any similar measure, not just the [Median Markup] example, just like it does with CALCULATE.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

AlexisOlson This solution appears to work when used in the same matrix as the 2b example. Including the total. Doubt it is the most efficient but seems to work.

 

Measure = 
    VAR __Stores = SUMMARIZE( 'Stores', [StoreKey] )
    VAR __Products = SUMMARIZE( FILTER( ALL('Product'), [ClassName] <> "Deluxe" ), [ProductKey] )
    VAR __Divisor = MEDIANX( FILTER( ALLSELECTED('Sales'), 'Sales'[StoreKey] IN __Stores && 'Sales'[ProductKey] IN __Products ), -1 + 'Sales'[SalesAmount] / 'Sales'[TotalCost] )
    VAR __Numerator = [Median Markup]
    VAR __Result = DIVIDE( [Median Markup], __Divisor )
RETURN
    __Result