Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
**edited to clarify terminology**
Hi,
New to Power Bi and DAX and struggling with the following issue.
I have a very large dataset with properties similar to the below example data. I am trying to identify if my data for the year 2020 is unusual (because of covid). My analysis is being done at Faculty level.
A simplification of what I would like to do is filter my list of subjects from 2020 based on if they are an outlier inside a Faculty based on 2019 data. To make the example simple here I will just call an outlier >Quartile 3 (Q3) or <Quartile 1 (Q1) of the 2019 data.
Calculating Q1 and Q3 for each faculty is easily done with a measure. However I do not want to hard code those values anywhere for a number of reasons.
So what I need in the below table is a calculated column "outlier" that identifies for a subject in 2020 in a particular Faculty if the average mark is >Q3 or <Q1 for the same faculty for 2019 data. But this value needs to be dynamic, so for example if I realise subject C should be in Faculty 1 instead of 2, a recalculation is done to redetermine the outlier status of this subject, etc.
Logically what I need but because of my lack of DAX knowledge cannot construct is:
=IF(OR([Average Mark] > (Dynamic Value of Q3 for Subject's Faculty and Year = 2019), [Average Mark] < (Dynamic Value Q1 for Subject's Faculty and Year = 2019), "Outlier", "Not Outlier")
Any help, hints, directions to similar postings appreciated.
Subject | Faculty | Year | Average Mark |
A | 1 | 2020 | 50 |
B | 1 | 2020 | 60 |
C | 2 | 2020 | 60 |
D | 2 | 2020 | 55 |
E | 3 | 2020 | 65 |
F | 3 | 2020 | 75 |
A | 1 | 2019 | 85 |
B | 1 | 2019 | 55 |
C | 2 | 2019 | 65 |
D | 2 | 2019 | 60 |
E | 3 | 2019 | 35 |
F | 3 | 2019 | 45 |
Solved! Go to Solution.
Sorry, I should have been clearer. Q1 and Q3 are quartile 1 and quartile 3, not quarter 1 and quarter 3. This is an attempt to identify "outliers" in exam results data, the only time variable is year.
So Q3 = percentile.inc([Average Mark], 0.75) for example.
I will re-edit original post for clarity.
Definitely, thanks very much
@mgrehandcu , I do not see any qtr in data.
Typically when I do not have the date, Try to add a date and try to use date table and time intelligence. Else I move time-related data to a new table. Create a rank and do it.
Using Rank
uning Time Intelligence 2
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Creating date when Date is not there
Within first few mins -https://www.youtube.com/watch?v=yPQ9UV37LOU&t=28s
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
44 | |
16 | |
12 |