Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure Difficulties

Hi Team,    I have a simple table with 3 columns.    I have been tasked to create a measure. The client wants to see the Average sales per customer where the SalesDate falls in between the period...
Need Help
Tutorial Requests
  • MFelix's avatar
    MFelix
    6 years ago

    Hello @water_hydration ,

    This is related to the filter context should be after the AVERAGEX and not within AVERAGEX:

    12 Month Sales =
VAR __startcurrentmonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR __previous12 =
    EDATE ( __startcurrentmonth, -12 )
RETURN
    CALCULATE (
        AVERAGEX ( SalesTable, AVERAGE ( SalesTable[Sales ($)] ) ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[SalesDate] <= __startcurrentmonth
                && SalesTable[SalesDate] >= __previous12
        )
    )

MFelix's avatar
MFelix
Super User
6 years ago

Hello @water_hydration ,

This is related to the filter context should be after the AVERAGEX and not within AVERAGEX:

12 Month Sales =
VAR __startcurrentmonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR __previous12 =
    EDATE ( __startcurrentmonth, -12 )
RETURN
    CALCULATE (
        AVERAGEX ( SalesTable, AVERAGE ( SalesTable[Sales ($)] ) ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[SalesDate] <= __startcurrentmonth
                && SalesTable[SalesDate] >= __previous12
        )
    )

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks so much MFelix !

 

Can you explain the last bit from the return? 

 

I am a little confused as to how it works. The only bit I had to eliminate was the 'All' from the function as it kept returning the same number per customer. 

 

RETURN
    CALCULATE (
        AVERAGEX ( SalesTable, AVERAGE ( SalesTable[Sales ($)] ) ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[SalesDate] <= __startcurrentmonth
                && SalesTable[SalesDate] >= __previous12
        )
    )

 

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    When you use variable you need to start the calculation with a return, basically what this measure is calculating is the AVERAGE value for each row of data within the sales table but to the calculation (CALCULATE) it apply a filter that is the second part of the CALCULATE so it only runs the AVERAGEX for all values contained within the dates considered on the dates you define on the variables.

     

    The ALL part of the solution was a mistake from my part since I have taken out the filter to the calculate if you place the ALL it will remove all filter context and return the same value for all lines in your case client. The ALL is used when you wan to make percentage over total for example.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much MFelix