Measure to COUNT number of values greater, within or smaller than dynamic range

[Anonymous]

Hi all,

I'm new to Power BI and DAX coding. I have 2 queries linked by an active relationship. In the first, I have employee salary data and employee IDs. Employees are segmented into different job families and job levels. In the second query, I have recommended salary ranges (min, mid, max) per each combo of job family & job level.

I want to add a doughnut chart to my dashboard showing the % of salary data below, within or above the recommended range for any given combo of job family & job grade. I think I'm after a dynamic COUNTIF function with the IF threshold changing according to the job family & job level selected. I'd be grateful for any tips on how to write the measure expression on DAX.

The only parameter varying is the job level to keep the below example simple. However, in my original dataset, I have different salary ranges by different combinations of job family & job sub-family & job grade & city. 

Query 1 

Identifier	Job Family	Job sub-family	Job Level	City	Employee ID	Total Package
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110498	70000
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110499	65000
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110500	60000
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110501	55000
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110502	75000
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110503	80000
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110504	85000
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110505	60000
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	110506	55000
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	110507	70000
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	110508	80000
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	110509	79000
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	110510	77000
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	110511	90000
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	110512	72000
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	110513	82500
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	110514	85000

Query 2

Identifier	Job Family	Job sub-family	Job Level	City	Range Min	Range Mid	Range Max
Marketing & Brand Management & 1-2 & London	Marketing	Brand Management	1-2	London	60000	67500	75000
Marketing & Brand Management & 2-2 & London	Marketing	Brand Management	2-2	London	75000	82500	87000

Thank you in advance to who might be able to help

[V-lianl-msft · Accepted solution]

Please try to create a new column:

condition = SWITCH(TRUE(),
            Query1[Total Package]<RELATED('Query 2'[Range Min]),"Smaller",
            Query1[Total Package]>RELATED('Query 2'[Range Max]),"Greater",
            Query1[Total Package]>=RELATED('Query 2'[Range Min])&&Query1[Total Package]<=RELATED('Query 2'[Range Max]),"Within")

[Anonymous]

Brilliant, you're a star! I enclose a SS of my own chart.

Minor point - I had to add a " ' " before and after Query1 for the new column formula to work. 

Thank you very much

Giac

[amitchandak]

@Anonymous , Not very clear. But seems like you need binning /segmentation on measure using an independent table

refer: https://youtu.be/CuczXPj0N-k

or
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

@amitchandak I reposted this with the sample data