Create Bins of Calculated Measures then Visualize them with Slicers

[DB_476]

I have a fun story problem that has proven more challenging than it may seem.  I am evaluating two different types of environmental "monitor" equipment that are placed for a period of time at the same "site" and then moved.  Each peice of equipment has different sensors but they are supposed to produce the same "record".  Each type of record has a set value of integer "points" associated with it.  There is a 1:1 relationship between the record" and the points it is worth (e.g. Record "H1" is worth 1 point regardless of site or monitor).  But each record has varying levels of decimal "concentration" associated with it.

I need to do a few simple comparisons:

1. Calculate, chart, then compare the percentage difference for total number of points that each monitor recorded per site (No problem!)
2. Group those point totals into bins (Slightly more challenging to bin the results of a measure, but can be done.)
3. Calculate, chart, then compare the percentage difference for total count of sites in each bin for each monitor (I'm stuck here.)

...here's the rub, I need to be able to do all the above and maintain the full functionality of slicers to see how they adjust the point sums and bin counts on-the-fly.  For example, I need to be able to select only certain records, within a certain concentration range, and see how it changes the sums and counts.

Here is a sample of my data (it really does come as one large file):

SiteID	Monitor	Record	Concentration	Points
ABC123	ClassAlpha	H1	0.207058836	1
ABC123	ClassAlpha	P3	0.609666331	3
ABC123	ClassAlpha	H2	0.232413664	2
ABC123	BetaModel	R6	0.056751379	4
ABC123	BetaModel	J9	0.269408806	3
ABC123	BetaModel	S8	0.666212848	1
ABC123	BetaModel	H6	0.668040701	2
LMN456	ClassAlpha	Q4	0.971143334	1
LMN456	ClassAlpha	P8	0.05853574	4
LMN456	BetaModel	H6	0.173882895	2
LMN456	BetaModel	H8	0.839264654	2
LMN456	BetaModel	J8	0.437778553	3
XYZ789	ClassAlpha	S8	0.971553633	1
XYZ789	ClassAlpha	H8	0.194524277	2
XYZ789	ClassAlpha	P3	0.918271584	3
XYZ789	ClassAlpha	P5	0.566951584	3
XYZ789	BetaModel	J1	0.824200962	5
XYZ789	BetaModel	Q7	0.847382764	4

Here is the table I can easily make:

Site	BetaModel_PointSum	ClassAlpha_PointSum	Percentage Difference
ABC123	10	6	1.66
LMN456	7	5	1.4
XYZ789	9	9	1

Here is the desired output I cannot make:

BinName_RangeOfPointSums	BetaBinCount	AlphaBinCount	Percentage Difference
<5	0	0	0%
5-10	2	3	150%
10-15	1	0	-100%
15-20	0	0	0%
20-25	0	0	0%

...and I need to be able to chart and slice all of the above to see the effects on sums and bin counts.

Thanks very much in advance!

[V-lianl-msft · Accepted solution]

Hi @DB_476 ,

Firstly, you need to create a table that holds your Bin names and Bin ranges.

Then create a measure to sum the points

Total points = CALCULATE(SUM('Table'[Points]),FILTER(ALLEXCEPT('Table','Table'[SiteID]),'Table'[Monitor]="BetaModel"))

Create a measure based on the created bin range as a filter condition.

Measure = var test = IF(HASONEVALUE('Table (2)'[BIN_Range]), CALCULATE(DISTINCTCOUNT('Table'[SiteID]),
FILTER('Table',[Total points]>=VALUES('Table (2)'[Min_value])&& 
[Total points]<VALUES('Table (2)'[Max_value])&&
'Table'[Monitor]="BetaModel" ) ),
DISTINCTCOUNT('Table'[SiteID]))
return IF(ISBLANK(test),0,test)

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.