Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Histogram with dynamic axis

Hi PBI Community,   I am trying to build a report that uses a histogram to show the distribution in occupancy and I need help. Pbix below   https://www.dropbox.com/s/xzq95otm3ki14b2/Histogram%20q...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    After a lot test, i find it.

    Please try to create a table first as below:

    X-Axis = GENERATESERIES(0,1,0.01)

    Then create a measure:

    Measure Value = COUNTROWS(FILTER(SUMMARIZE('Census','Census'[Date],"AVG",ROUND([Average Occupancy],2) ),[AVG] = ROUND(SELECTEDVALUE('X-Axis'[Value]),2)))

    When you select all in two slicers, it shows as my original reply:

    When you select North only in Building slicer, the x-axis has dynamically changed:

    Here is my test pbix file:

    pbix 

    If i have solved your issue, please mark my answer as a solution for others to see.

    Thanks!

     

    Best Regards,

    Giotto Zhi

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try this measure to replace the original Count of Date value:

Measure = CALCULATE(COUNTROWS(Census),FILTER(Census,Census[Building]=SELECTEDVALUE(Census[Building])&&Census[Category]=SELECTEDVALUE(Census[Category])))

When you choose the slicer, the result shows:

 

Hope this helps.

 

Best Regards,

Giotto Zhi

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks for your suggestion!

 

I tried the measure and it's still not quite giving me what I need. 

 

Here is a revised pbix file: https://www.dropbox.com/s/xzq95otm3ki14b2/Histogram%20question.pbix?dl=0 

 

All the filters and slicers will impact the table visual. I would like the histogram to represent the data in the total column of that table. So in the example here, there should only be seven values in the histogram:

1 74%

1 75%

1 77%

3 82%

1 83%

I hope that's a better explanation of the problem. Thanks again for your help!

 

 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try to create this calculated table first:

    Table = SUMMARIZE(Census,Census[Date],"Column",[Average Occupancy])

    Then create a measure to count date:

    Measure 2 = DISTINCTCOUNT(Census[Date])

    The result shows:

    Here is the changed pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks! This is really close. 

       

      The histogram in the file does represent the table as is, but if I apply any filters (e.g., just looking at the north building) then the histogram does not change. Any idea on how to enable that functionality?

       

      Thanks again - I really appreciate your thoughts!

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        Hi,

         

        After a lot test, i find it.

        Please try to create a table first as below:

        X-Axis = GENERATESERIES(0,1,0.01)

        Then create a measure:

        Measure Value = COUNTROWS(FILTER(SUMMARIZE('Census','Census'[Date],"AVG",ROUND([Average Occupancy],2) ),[AVG] = ROUND(SELECTEDVALUE('X-Axis'[Value]),2)))

        When you select all in two slicers, it shows as my original reply:

        When you select North only in Building slicer, the x-axis has dynamically changed:

        Here is my test pbix file:

        pbix 

        If i have solved your issue, please mark my answer as a solution for others to see.

        Thanks!

         

        Best Regards,

        Giotto Zhi