Forum Discussion

swong's avatar
swong
Icon for Helper II rankHelper II
9 years ago
Solved

Average over time vs. sliced average

Hi,   I'm hoping to use PowerBI to show overall market trends as well as trends in individual accounts. My data has several different slicers, e.g. location, device type, etc.   Is is possible fo...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi swong,

     

    I have reviewed your shared pbix file. And I find that there is no problem with the formula of the measures. Instead of using Date hierarchies as Axis, you should use the Date column itself as Axis, then it will work as expected. :smileyhappy:

     

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

swong's avatar
swong
Icon for Helper II rankHelper II
9 years ago

Oh I see. I understand that definition, and now my formula is

 

Overall = CALCULATE(AVERAGE(Sheet1[Revenue]), ALLEXCEPT(Sheet1, Sheet1[Date]))

 

But this still gives me the same graph with the straight black line.

 

For reference, my individual formula is:

 

Individual = AVERAGE(Sheet1[Revenue])

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi swong,

 

What columns are you using as Slicers on the report? Are these columns in another table? If that is the case could you try using ALL function with these columns in the measure like below to see if it works? :smileyhappy:

Overall =
CALCULATE (
    AVERAGE ( Sheet1[Revenue] ),
    ALL ( Sheet2[Location] ),
    ALL ( Sheet3[Device Type] )
)

 

Regards