Search code examples
exceldaxpowerpivot

Return DAX Measure created from Table VAR that ignores filter context

I have a table that looks like this:

enter image description here

I've loaded this into Excel PowerPivot and am trying to create a normalized/indexed column that I can use in pivot table. For reasons I wont go into, I'm trying to do this via a Measure vs a Calculated Column.

Here is the code for my Measure:

Measure 1:=VAR Group1 = GROUPBY('Test','Test'[Country],'Test'[Date],"Visits1", SUMX(CURRENTGROUP(),'Test'[Visits])) VAR Group2 = GROUPBY(Group1,[Country],"Visits2", MAXX(CURRENTGROUP(),[Visits1])) RETURN CALCULATE(MAXX(Group2,[Visits2]),ALL('Test'[Date]))

enter image description here

I'm pretty novice with DAX, but this is what I assume is happening when I create my Table VARs:

enter image description here

Using this Measure, when I go and create a Pivot Table, I get this (I've manually added the yellow columns):

enter image description here

As you can see, 'Measure 1', does not equal my 'Expected' column. I've tried a whole bunch of ways to use ALL() in my RETURN statement, but I cannot get it to work. Can anyone help?

Oh, and once I achieve this my goal was to indexed value as seen in my final yellow column using the pseudo formula below.

Row value / Max column value (but filtered by country) * 100

Solution 
  • [measure] :=
VAR MaxVisits =
    CALCULATE (
        MAXX ( VALUES ( Visits[Date] ), CALCULATE ( SUM ( Visits[Visits] ) ) ),
        ALL ( Visits[Date] )
    )
VAR CurrentVisit =
    CALCULATE (
        MAXX ( VALUES ( Visits[Date] ), CALCULATE ( SUM ( Visits[Visits] ) ) )
    )
VAR Ratio =
    DIVIDE ( CurrentVisit, MaxVisits ) * 100
RETURN
    Ratio