Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I want to calculate an average line for 3 days across the time interval period. I want to get an average Black line as shown in the below dig.
Can anyone guide me with the solution.
Solved! Go to Solution.
Hi @siva3012,
Based on my test, if we have already add a legend for line chart, it is not allowed to add multiple columns or measures into values section. In other words, we can generate a measure to return average value, but we are unable to add this measure to line chart.
As a workaround, we can create multiple measures to get result for each legend item separately.
Base on below sample data, we could create measures like:
Sum for 1 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=1)) Sum for 2 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=2)) Sum for 3 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=3)) Average = CALCULATE ( SUM ( 'Table'[DurationDays] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ) / CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) )
Best regards,
Yuliana Gu
Hi @siva3012,
Based on my test, if we have already add a legend for line chart, it is not allowed to add multiple columns or measures into values section. In other words, we can generate a measure to return average value, but we are unable to add this measure to line chart.
As a workaround, we can create multiple measures to get result for each legend item separately.
Base on below sample data, we could create measures like:
Sum for 1 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=1)) Sum for 2 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=2)) Sum for 3 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=3)) Average = CALCULATE ( SUM ( 'Table'[DurationDays] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ) / CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) )
Best regards,
Yuliana Gu
How would you then add a filter on top of this to see just the average line and say "Sum for 1"? Essentially filtering the measures?
@v-yulgu-msft : Thanks for the help. I also think this is the only way to approach.
I have one query - How can i calculate the percentage between the Average line & other lines 1,2&3.
In simple words i want to know whether these values/lines (1,2&3) will be 5% +/- from the average line ?
Do you have any idea about it ?
Hi @siva3012,
Have you achieved your requirement? If yes, would you please mark the helpful reply as an answer or share your solution so that it can benefit more users?
Regards,
Yuliana Gu
Hi @siva3012,
Since the sum values and average values are all returned by measures, we can directly compute the percentage via:
Percentage1 = ([Sum for 1]-[Average])/[Average]
Percentage2 = ([Sum for 2]-[Average])/[Average]
Percentage3 = ([Sum for 3]-[Average])/[Average]
Regards,
Yuliana Gu
@Anonymous : I tried the average line from Analytics plane and it draws an straingt line , i have shown it in the dig.
Yeah, I missed that.. Let me try another way
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |