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
Microsoft EmployeeHi swong,
Is is possible for me to show two lines on a line graph, one for the overall marketplace average, and one for the individual account selected?
Using ALL/ALLEXCEPT could achieve it as Anonymous has mentioned above. The formulas below are for your reference.
overall marketplace average =
CALCULATE (
AVERAGE ( 'RevenueTable'[Revenue] ),
ALLEXCEPT ( RevenueTable, 'RevenueTable'[Column_Name_that_you_used_for_Axis] )
)
individual account selected = SUM ( 'RevenueTable'[Revenue] )
If you still cannot figure out the measure to do it, you can just post your table structures with some sample/mock data and your expected result. So that we can better assist on this issue. :smileyhappy:
Regards
Helper III think I'm understanding a little more, but I'm still confused about the Column_Name_that_you_used_for_Axis
This is my example data. When I used the formulas you suggested (I changed the individual SUM to an AVERAGE because I would like to compare an individual average gainst the marketplace overall), I got this graph:
I was hoping for th black line to look more like this teal one below, which is the average of the overal marketplace broken down by day.
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.
- swong
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
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