Forum Discussion

hamachekm's avatar
hamachekm
Helper I
5 years ago
Solved

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).    T...
Need Help
  • hamachekm's avatar
    hamachekm
    5 years ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

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.
hamachekm's avatar
hamachekm
Helper I
5 years ago

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