Forum Discussion
Helper IIAverage over time vs. sliced average
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
The [Column Name that you used for Axis] is the column that you have placed into the X-Axis area of your graph. As you have noted, you are getting a graph that is a straight line. The ALL method ignores all context, thus it doesn't matter where on the graph it is placed, it always gets the same answer.
ALLEXCEPT however, ignores all context except for the context provided in the columns you specify. So by placing your X-Axis column into ALLEXCEPT, it will take that context into account.
Helper IIOh 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-msftMicrosoft Employee
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
- swong
I used to have slicers, but I took them off for simplicity/to figure this first issue out, but those columns weren't being used anywhere else.
Here's my dashboard: https://msit.powerbi.com/view?r=eyJrIjoiNzA2MDdiODQtZTNhNC00NTNkLWExM2MtNGJhMGY2YWU1ZTRiIiwidCI6IjcyZjk4OGJmLTg2ZjEtNDFhZi05MWFiLTJkN2NkMDExZGI0NyIsImMiOjV9
Why would the slicers affect the display?
- v-ljerr-msft
Hi swong,
A little weird! So could you share a sample pbix file(with just some sample/mock data) which can reproduce this issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:
Regards