Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good Day,
I hope someone can help me with this request. It seems like a simple problem but I am stumped as to how to handle this.
I have a simple dataset. It looks something like this.
ID | Risk Status | Numerator | Denominator | Progress - Measure (Ratio) | Prefered Method (Division only for On-Risk) |
111 | On-Risk | 12 | 15 | 0.8000 | 0.8000 |
222 | Off-Risk | 14 | 15 | 0.9333 | 1 |
333 | Inactive | 0 | 20 | 0.0000 | 0 |
444 | On-Risk | 1 | 15 | 0.0667 | 0.0667 |
555 | On-Risk | 22 | 25 | 0.8800 | 0.8800 |
666 | Off-Risk | 5 | 5 | 1.0000 | 1 |
777 | Inactive | 0 | 30 | 0.0000 | 0 |
What I am needing the is the last column. We have a table with the columns 1-4. Column 5 is just a simple measure that divides column 3 into 4. Basically to get a ratio of how much progress has been made a ratio.
The measure is written as such below
Solved! Go to Solution.
It's not clear to me what the desired result should be in such a case. Are you trying to calculate an average iterating over the rows of the fact table?
If so, just stick it inside an AVERAGEX.
Preferred Method =
AVERAGEX (
'Fact',
SWITCH (
'Fact'[Risk Status],
"On-Risk", [Progress Measure],
"Inactive", 0,
"Off-Risk", 1
)
)
This is fairly straightforward with a SWITCH.
Preferred Method =
SWITCH (
SELECTEDVALUE ( 'Fact'[Risk Status] ),
"On-Risk", [Progress Measure],
"Inactive", 0,
"Off-Risk", 1
)
Alexis, Thank you for the response. You are correct in that your response works for the problem I described. However, in my in-experience, I did not describe the problem very well.
My problem is that I did think about what would happen when this Measure gets used in other areas. For example, when we look at the full dataset, there is also a year. And if we group by that in a Matrix, we don't get the desired result.
Would you happen to know how that could be solved, please?
Btw, if you want, I will mark your previous answer as "Accept as Solution" if you so desire.
Thank you in advance.
George
It's not clear to me what the desired result should be in such a case. Are you trying to calculate an average iterating over the rows of the fact table?
If so, just stick it inside an AVERAGEX.
Preferred Method =
AVERAGEX (
'Fact',
SWITCH (
'Fact'[Risk Status],
"On-Risk", [Progress Measure],
"Inactive", 0,
"Off-Risk", 1
)
)
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |