Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How To create Distribution Chart(Bell chart) in Power BI

Hi , I'm Tableau Developer and very new to Power BI and got new request from my team to build a Distribution Chart(Bell Chart) in Power BI.   I have 2 Columns 1. Risk Score : I have created New C...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    For the color legend, you could split the line into 3 lines. Create measures for 3 Risk score separately and put all of them into Values. Then format them with different colors and turn on shade area. 

     

    f(x) High = IF('Normal Distribution'[X]<FLOOR([x-2],1)||'Normal Distribution'[X]>FLOOR([x+2],1),[f(x)],BLANK())

     

    f(x) Medium = IF(('Normal Distribution'[X]>FLOOR([x-2],1)&&'Normal Distribution'[X]<FLOOR([x-1],1))||('Normal Distribution'[X]>FLOOR([x+1],1)&&'Normal Distribution'[X]<FLOOR([x+2],1)),[f(x)],BLANK())

     

    f(x) Low = IF('Normal Distribution'[X]>FLOOR([x-1],1)&&'Normal Distribution'[X]<FLOOR([x+1],1),[f(x)],BLANK())

     

    For the count of Part number shown in Y axis, I don't know how to deal with it. Sorry.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    All of f(x) High / Medium / Low measures should be placed in the Line values rather than Column values. You can remove the original f(x) measure from Line values to check the result. They should connect then. Let me know if it works or not.

     

    Regards,

    Jing

  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    You should move them to Line values area below. 

    Jing

  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    Seems close, we need to do some formattings further. 

     

    • Under X axis, change Type from Continuous to Categorical. 
    • Right click on the X field in Shared axis and select Show items with no data option.
    • On the chart, click ... and select Sort by X and Sort ascending.

     

    And in the formulas, change all > and < signs to >= and <= to connect the boundry values. 

     

    Jing

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Ibendlin,

 

Thank you for quick response.

 

I have gone through the youtube videos and came up with below chart, but i would like to show in

Y axis  as count of Part number & color legend as low, medium and high.

 

Kindly help me to build it as above sample chart.

 

For your reference :

Normal Distribution =
var minval=FLOOR([x-3],1)
var maxval=CEILING([x+3],1)
return
SELECTCOLUMNS(CALENDAR(minval,maxval),"X",INT([Date]))
 
x-3 = [Mean Value]-3*[Std Dev]
x+3 = [Mean Value]+3*[Std Dev]
 
f(x) = EXP((VALUES('Normal Distribution'[X])-[Mean Value])^2/(2*[Std Dev]^2)*-1)/(SQRT(2*PI())*[Std Dev])
 
mu-1 = IF('Normal Distribution'[X]=FLOOR([x-1],1),[f(x)],BLANK())
 
mu+1 = IF('Normal Distribution'[X]=FLOOR([x+1],1),[f(x)],BLANK())
 
mu-2 = IF('Normal Distribution'[X]=FLOOR([x-2],1),[f(x)],BLANK())
 
mu+2 = IF('Normal Distribution'[X]=FLOOR([x+2],1),[f(x)],BLANK())
 
High - Red
Medium - Yellow
Low - Green
 
 

v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

Hi Anonymous 

 

For the color legend, you could split the line into 3 lines. Create measures for 3 Risk score separately and put all of them into Values. Then format them with different colors and turn on shade area. 

 

f(x) High = IF('Normal Distribution'[X]<FLOOR([x-2],1)||'Normal Distribution'[X]>FLOOR([x+2],1),[f(x)],BLANK())

 

f(x) Medium = IF(('Normal Distribution'[X]>FLOOR([x-2],1)&&'Normal Distribution'[X]<FLOOR([x-1],1))||('Normal Distribution'[X]>FLOOR([x+1],1)&&'Normal Distribution'[X]<FLOOR([x+2],1)),[f(x)],BLANK())

 

f(x) Low = IF('Normal Distribution'[X]>FLOOR([x-1],1)&&'Normal Distribution'[X]<FLOOR([x+1],1),[f(x)],BLANK())

 

For the count of Part number shown in Y axis, I don't know how to deal with it. Sorry.

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Jing,

     

    Thank you for your reply.

     

    I just  applied f(x) High formula, I'm getting below result. 

     

     

    Please do let me know if it is possible to connect.

    • v-jingzhang's avatar
      v-jingzhang
      Community Support
      5 years ago

      Hi Anonymous 

       

      All of f(x) High / Medium / Low measures should be placed in the Line values rather than Column values. You can remove the original f(x) measure from Line values to check the result. They should connect then. Let me know if it works or not.

       

      Regards,

      Jing

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Jing,

         

        When i applied your formula i got this result.

         

         

         

  • anjaliprabhakar's avatar
    anjaliprabhakar
    New Member
    1 year ago

    Hello all,

     

    Please help me to create Beel curve in Power BI as mentioned below in the attached screenshot for an example. The data which needs to be shown is 3 different standard deviation of the trend in 4 different ways:

     

    These are 3 different years standard deviation data which is shown for example. I need to create something similar.