Forum Discussion

efstel's avatar
efstel
Helper I
2 years ago

Group Values in a within a Calcuated Measure

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?

 
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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)

     

    • efstel's avatar
      efstel
      Helper I
      2 years ago

      Appreicate it Ashish_Mathur and Anonymous. 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.