Histogram with dynamic axis

[Anonymous]

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!

[v-gizhi-msft]

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]

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!

[v-gizhi-msft]

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]

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 · Accepted solution]

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

[Anonymous]

It worked! Thank you so much - you're amazing!

[Anonymous]

[Greg_Deckler]

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...