Adding own constant y line DAX

[Rutizz]

Dear community,

I’m struggling with adding a constant line to my chart.

• The red line is the build in ‘constant line’ mean.
• The green line is my own DAX formula ‘average’ of a column

Both lines should be the same, though the red line cumulates the numbers for different days and the green line takes an average of all days.

Example:

• Person A: DAY X ‘10’, DAY Y ’11’
• Person B: DAY X ‘8’, DAY Y ‘9’
• Person C: DAY X ‘2’, DAY Y ‘3’

Green line does the following: 10+11+8+9+2+3 = 43/6

Red line does the following: 43/3

I would like the know the DAX formula to get the red line: 'Adding a cumulative mean for multiple days divided by number of persons'. At the end I would like to multiply the formula with a percentage so I can't use the build in application..

Can anyone help me? Thanks in advance.

[Mikelytics]

Hi @Rutizz 

Can you please show how the udnerlying table looks like?

Lets say you habe t able with the following columns

- Person

- day

- value

then the formula for the red line would be

Average by person =

CALCULATE(
   DIVIDE(
      SUM(table[value]),
      DISTINCTCOUNT(table[Person])   
   ),
   ALL(Table[day]
)
   

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[v-xiaotang]

Hi  @Rutizz 

not clear, What is the formula for calculating the constant y line? please provide it then we can translate it with DAX language.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.