Forum Discussion
Histogram based on measure range
Hi,
I'd like to make a column chart to summarize the distribution of a measure by SalesPerson. (It is not possible to make the measure a calculated column or column in the original table).
The underlying table has three fields to make the key: SalesPerson-Year-Product
The measure is a function of a few inputs that vary by Year / Product for each SalesPerson. The measure is called [2022 Projection]. The value is continuous, let's say, varying from 500 to 1700.
Here's a simple example of a matrix table that I can make:
| Sales Person | [2022 Projection] | [Range of 2022 Projection] |
| 1 | 500 | <850 |
| 2 | 800 | <850 |
| 3 | 1250 | >1050 |
| 4 | 900 | 850 to 1050 |
| 5 | 1000 | 850 to 1050 |
I'd like to make a column bar chart with these values:
| Range of 2022 Projection | Sales People Count |
| <850 | 2 |
| 850 to 1050 | 2 |
| >1050 | 1 |
I've read a few other posts but haven't found a solution that works yet. Appreciate your help in advance.
https://community.powerbi.com/t5/Desktop/Histogram-User-defined-bin-size/m-p/69854#M28961
Thanks Ashish.
One edit to your measure: in the middle you write "Calculatetable (Values([2022 Projection]))... " . Because [2022 Projection] is a measure, how can you use the Values() function which expects a Column?
I dropped the CalculateTable(Values()) and just left [2022 Projection] and it works. Here's the full expression: Sales people count = COUNTROWS(FILTER(ADDCOLUMNS(VALUES('Date'[Sales person]),"ABCD",CALCULATE([2022 Projection],[2022 Projection])))),COUNTROWS(FILTER('Buckets',[ABCD]>='Buckets'[Lower]&&[ABCD]<'Buckets'[Upper]))>0))
For completeness, this is the measure that goes in the Values of the bar graph. The Axis is the Bucket column. I also added an Order column to sort the Bucket on table too.
Thanks,
Mike
3 Replies
Hi,
Create a simple 2 table like this one
Lower Upper Bucket 0 850 <850 850 1050 850-1050 1050 1000000 >850 Sort the third column by the first column. Name this table as Buckets. Write this measure
Sales people count = COUNTROWS(FILTER(ADDCOLUMNS(VALUES('Date'[Sales person]),"ABCD",CALCULATE([2022 Projection],CALCULATETABLE(VALUES(2022 Projection])))),COUNTROWS(FILTER('Buckets',[abcd]>='Buckets'[Lower]&&[ABCD]<'Buckets'[Upper]))>0))
Hope this helps.Thanks Ashish.
One edit to your measure: in the middle you write "Calculatetable (Values([2022 Projection]))... " . Because [2022 Projection] is a measure, how can you use the Values() function which expects a Column?
I dropped the CalculateTable(Values()) and just left [2022 Projection] and it works. Here's the full expression: Sales people count = COUNTROWS(FILTER(ADDCOLUMNS(VALUES('Date'[Sales person]),"ABCD",CALCULATE([2022 Projection],[2022 Projection])))),COUNTROWS(FILTER('Buckets',[ABCD]>='Buckets'[Lower]&&[ABCD]<'Buckets'[Upper]))>0))
For completeness, this is the measure that goes in the Values of the bar graph. The Axis is the Bucket column. I also added an Order column to sort the Bucket on table too.
Thanks,
Mike
This is what happend when i write a measure without having access to the PBI file. The VALUES(2022 Projection]) should be VALUES('Date'[Sales person])
