The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi. Given a table of values, something like below, I want a measure that will return the "within range" percentage for each category. So if 3 out of 4 values are with in range (as shown below) the measure should return 0.75 for category A.
Table1
Category..... | RangeHigh..... | RangeLow..... | Value |
A | 30 | 50 | 52 |
A | 30 | 50 | 45 |
A | 30 | 50 | 40 |
A | 30 | 50 | 38 |
[extra dots for formatting]
What I've come up with is below, but the problems are:
PctInRange = 1 - CALCULATE(COUNTROWS(Table1), FILTER(Table1, (Table1[Value] < Table1[RangeLow] || Table1[Value] > Table1[RangeHigh]) && Table1[Category] = "A")) / CALCULATE(COUNTROWS(Table1), Table1[Category] = "A")
Can someone help? Thanks for taking a look!
Solved! Go to Solution.
A few thoughts and a question.
Those combined would give:
PctInRange =
VAR tblGroupAInRange =
FILTER(
Table1,
(Table1[Value] < Table1[RangeLow] || Table1[Value] > Table1[RangeHigh])
&& Table1[Category] = "A")
VAR cntGroupAInRange = COUNTROWS ( tblGroupAInRange )
VAR cntGroupA =
CALCULATE(
COUNTROWS ( Table1 ),
Table1[Category] = "A"
)
VAR Result = 1 - DIVIDE ( cntGroupAInRange, cntGroupA)
RETURN Result
In terms of making it more dynamic depending on how you're intending to use the measure you could just get rid of the hard coded category = A filters. Sliceing on category would pass the filter straight into both.
Lastly are the high/low values the same for all of category A? If so moving them out to a category dimension might help further.
Share a little on how you'd like to use the measure and we can take it further.
A few thoughts and a question.
Those combined would give:
PctInRange =
VAR tblGroupAInRange =
FILTER(
Table1,
(Table1[Value] < Table1[RangeLow] || Table1[Value] > Table1[RangeHigh])
&& Table1[Category] = "A")
VAR cntGroupAInRange = COUNTROWS ( tblGroupAInRange )
VAR cntGroupA =
CALCULATE(
COUNTROWS ( Table1 ),
Table1[Category] = "A"
)
VAR Result = 1 - DIVIDE ( cntGroupAInRange, cntGroupA)
RETURN Result
In terms of making it more dynamic depending on how you're intending to use the measure you could just get rid of the hard coded category = A filters. Sliceing on category would pass the filter straight into both.
Lastly are the high/low values the same for all of category A? If so moving them out to a category dimension might help further.
Share a little on how you'd like to use the measure and we can take it further.
I am thinking you could accomplish this using a calculated column in table 1 that tests whether a Value is in range and returns something like "isInRange" or notInRange". Then write a measure that divides the count of "isInRange" by all values in the calculated column to get the percentage. That should allow filtering and slicing by categories that are in Table 1.
Proud to be a Super User! | |
I could do that but I don't want to add excessive overhead to the table if it could all be managed in DAX without impacing performance, which I think it can.
Can someone suggest how to modify the DAX, please?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the July 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
112 | |
69 | |
46 | |
43 |
User | Count |
---|---|
186 | |
85 | |
76 | |
74 | |
56 |