Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Control Chart in Power BI

Hi All, 

 

I am trying to create a control chart using power bi. 

I have the date column in my data table name "Date". I have the parameters in another column (oil variance)

 

So to create the average line, I use this calculation:

Average Oil Variance = CALCULATE(AVERAGEX(DATA,DATA[Oil Variance]),ALLSELECTED(DATA[Date]))
 
The line shows the correct number but the thing is it is now appear for the whole year no regards to my slicer. For example below is the snapshot when I choose April (1st pic) and July (2nd pic)
 

 

The navy blue line it self shows correct number, so when I choose April it shows average of April, when I choose July it shows average of July.

 

What I want is, when I choose the slicer, the axis date will adjust themselves to only the selected month (before average line was there, it do what I want).

 

I try to modified the calculation to:

Average Oil Variance = CALCULATE(AVERAGEX(DATA[Date],DATA[Oil Variance]),ALLSELECTED(DATA[Date]))

 

But it give error

 

 

Any idea how to overcome this?

 

Thanks in advance.


Best Regards,

Handy Pratama

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi Anonymous ,

     

    According to the error message, the averagex function returns the average value of the expression calculated for each row in the table, AVERAGEX (<table name>, <expression> ). "Table name" is a table that uses expression to evaluate each row: "Expression" is an expression that is evaluated for each row of the table. Refer to the following formula.

    Average Oil Variance = 
CALCULATE (
    AVERAGEX ( 'DATA', DATA[Oil Variance] ),
    ALLSELECTED ( DATA[Date] )
)


    If the problem is still not resolved, can you provide an effective data model (delete sensitive information), I will answer you as soon as possible.


    Best Regards,
    Henry


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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Is the axis is drived from DATA[Date], I doubt that.

    I think it is using date or calendar table.

     

    If yes use ALLSELECTED(Date[Date])

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak 


    Thank you for your reply. I try your solution and it gave me this error

     

    I think its coming from the Data[Date] As i have date as column in my data table. I really new to power BI so I am not quite sure if I doing it right.

     

    Do you have further advice?

     

    Thanks in advance.


    Best Regards,
    Handy Pratama

     

     

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi Anonymous ,

     

    According to the error message, the averagex function returns the average value of the expression calculated for each row in the table, AVERAGEX (<table name>, <expression> ). "Table name" is a table that uses expression to evaluate each row: "Expression" is an expression that is evaluated for each row of the table. Refer to the following formula.

    Average Oil Variance = 
CALCULATE (
    AVERAGEX ( 'DATA', DATA[Oil Variance] ),
    ALLSELECTED ( DATA[Date] )
)


    If the problem is still not resolved, can you provide an effective data model (delete sensitive information), I will answer you as soon as possible.


    Best Regards,
    Henry


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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank You all for your reply I already accept as solution.