Forum Discussion
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%20question.pbix?dl=0
In the example, I am trying to show visuals for occupancy = number of guests / number of rooms. In the table, you can see how the occupancy breaks down by building (north or south) and category (a or b, indicating whether it's a suite or a regular room). To calculate aggregate occupancies, I need do a summation of the census and beds based on the slicers. It is not accurate to average occupancies accross buildings and categories.
Here is my problem: In the histogram, I need to enter a calculated column as my axis. In doing so, I am unable to filter the histogram for buildings and categories without getting an inaccurate graph.
Anyone have any methods to make the histogram axis dynamic based on the selected slicers?
Thanks for your help!
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:
If i have solved your issue, please mark my answer as a solution for others to see.
Thanks!
Best Regards,
Giotto Zhi
8 Replies
Will see if I can take a look at the PBIX. But, in general, I think some variation of the Disconnected Table Trick? https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
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
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!
