Forum Discussion
Super UserNo CALCULATE Challenge -- Round #2b
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.
Community ChampionAlexisOlson I don't believe that the CALCULATE formula is returning the correct results. Here's why. If I add up all of the non-China distinct counts in Economy for Asia that are not China then I get 23,925. This would be a maximum number but the CALCULATE formula returns 28,189. There's no way to get that number really without adding in China's numbers to get you to 29,588 and then probably have some duplicates maybe? The point is though, I don't see how that number is possible quite frankly without ignoring the Filters pane filters for RegionCountryName which I'm not sure is or is not correct/intended.
Therefore, I would first like to ask that you prove that the 28,189 number is, in fact, correct and not something being made up by CALCULATE. Is it intended that it preserve all filters but ignore the filters in the Filters pane for RegionCountryName?
The solution would be the following for how the CALCULATE is working, which I feel isn't correct or at least extremely non-intuitive for the end user. I want to credit lbendlin here as this measure is based off of his work.
DONC_D =
VAR __Continent = MAX( 'Geography'[ContinentName] )
VAR b =
SUMMARIZE(
FILTER(
ALL( Geography),
[ContinentName] = __Continent && [RegionCountryName] <> "China"
),
'Geography'[RegionCountryName],
"do", DISTINCTCOUNT( Sales[OrderKey] )
)
RETURN
SUMX( b,[do] )
Super UserGreg_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
AlexisOlson The fixing of the grand total is a very straight forward solve. Jusy yet another example of incorrect measure totals in Power BI. Honestly, Power BI gets the total wrong so often, I don't even pay attention to it any longer just assuming that it is wrong as I typically turn that garbage off anyway.
- Greg_Deckler
AlexisOlson So is are these measures used in the same matrix visual or a different visual?
- AlexisOlson
Here's a visual to check against (no slicer or visual filters applied). I've included my [Markup Non-Deluxe Ratio] measure along with both versions you've suggested so far.
- Greg_Deckler
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 - Greg_Deckler
AlexisOlson Here is another version that is twice as fast and almost as fast as CALCULATE:
MRNC = VAR __Stores = SUMMARIZE( 'Stores', [StoreKey] ) VAR __Products = SUMMARIZE( FILTER( ALL( 'Product' ), [ClassName] <> "Deluxe" ), [ProductKey] ) VAR __Table = NATURALLEFTOUTERJOIN( NATURALLEFTOUTERJOIN( __Stores, ALLSELECTED( 'Sales' ) ), __Products ) VAR __Divisor = MEDIANX( __Table, -1 + [SalesAmount] / [TotalCost] ) VAR __Numerator = [Median Markup] VAR __Result = DIVIDE( __Numerator, __Divisor ) RETURN __Result