Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Histogram by Percentage

I have to recreate the following graph from excel in Power BI 

Original Excel Graph

My data looks like the following:

My Data

As you can see, I need to split up the data into blocks (e.g. 0-100, 100-200...) and then calculate the percentage of the values in each block and then graph that. I am new to Power BI and have tried playing around with it, but cannot work out how to create this graph. Thanks in advance.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Enter data to create a new table

    Create calculated columns

    bucket = SWITCH(TRUE(),[value]>=0&&[value]<1000,"1000",[value]>=1000&&[value]<2000,"2000",[value]>=2000&&[value]<3000,"3000")

    Create measures

    frequency = CALCULATE(COUNT('Table'[value]),ALLEXCEPT('x table','x table'[x_values]))

percentage = [frequency]/CALCULATE(COUNT('Table'[value]),ALL('Table'))

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Perfect! Yep that has allowed me to create a histogram from my data, thank you. However, any idea how to change the y axis from frequency to percentage?

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous 

        Create a calcualated column

         

        bucket =
SWITCH (
    TRUE (),
    [value] >= 0
        && [value] < 100, "0~100",
    [value] >= 100
        && [value] < 200, "100~200",
    [value] >= 200
        && [value] < 300, "200~300",
    [value] >= 300
        && [value] < 400, "300~400",
    [value] >= 400
        && [value] < 500, "400~500"
)

         

        Create measures

         

        frequency = CALCULATE(COUNT('Table'[value]),ALLEXCEPT('Table','Table'[bucket]))

percentage = [frequency]/CALCULATE(COUNT('Table'[value]),ALL('Table'))