Forum Discussion
AlexisOlson
Super User
Super UserNo 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...
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])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.
lbendlinSuper User
Super User
DONCB =
VAR b = SUMMARIZECOLUMNS(
Geography[RegionCountryName],
FILTER(
ALL(Geography[RegionCountryName]),
[RegionCountryName] <> "China"
),
"do", [Distinct Orders]
)
RETURN
SUMX(
b,
[do]
)Looking at the results they seem to be off though. China accounts for 5.663 distinct order keys but the value without China only goes down by 1399.
I think a more appropriate measure would be
DONCB =
var b = SUMMARIZECOLUMNS(Geography[RegionCountryName],FILTER(ALLSELECTED(Geography[RegionCountryName]),[RegionCountryName]<>"China"),"do",DISTINCTCOUNT(Sales[OrderKey]))
return sumx(b,[do])