Group Values in a within a Calcuated Measure

[efstel]

I know I can't do this, but I'm sure there is a work around but I can't figure it out. So here's the deal:

I have two large sets of data tables. One has costs ('JCCD'[ActualCost]) and the other billings ('JCID'[BilledAmt]). I'm doing a simple division measure to determine the % difference. Both data sets have a "JobNumber" associated with each line of cost and billings. I can aggregate all the costs and billing from each JobNumber together and get the % difference no problem via:

ActualCost divided by BilledAmt = DIVIDE(SUM('JCCD'[ActualCost]), SUM('JCID'[BilledAmt]))
I would like to group the values in groups of 5% or so so I can see how many (count) "JobNumber"s fall into each group. For example, 80%-85%, 85%-90%, 90%-95%,...
Any suggestions?

[Ashish_Mathur]

Hi,

You may refer to my solution to a similar problem in the attached files.  Please study/review the files and adapt the techniques/measures to your data.

Hope this helps.

[efstel]

Appreicate it @Ashish_Mathur and @RossEdwards. Both of these solutions partially work, but what I need is for the query to influence the table when the visual is clicked. For example, if I have a bar on a bar chart showing the 90%-95% and if I were to click on that bar I would want it to query the table to only show the 90% - 95% group. Currently now I'm getting the following.  It's just toggling the text off and on.

[RossEdwards]

Try something like this:

ActualCost divided by BilledAmt Grouped = var resultValue = [ActualCost divided by BilledAmt]
RETURN
SWITCH(
    TRUE,
    resultValue < 0.05, "0% - 5%",
    resultValue < 0.1, "5% - 10%",
.
.
.
    resultValue < 0.9, "90% - 95%",
    "95% - 100%"
)

(note, those dotted rows are so i dont have to write the whole thing out in my illustration. You will have to)