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.
Hi all,
I have a very large set of data (nearly a million rows) and I am using filters in the dashboard to get it to show a table of just the data I need to show. ( I need the filter in the dashboard to users can do this themselves)
In this instance the 3 colums I have picked out and filtered are "Provider" "Course Name" "Student Numbers".
For the Example I have a filter on "Course Name" to show just Economics and a filter on "Provider" as there is 1 provider I don't wan to count.
Now I have this table in the report with 12 providers and the number of students at each doing Economics.. What I need I want to show now is the Median number of students for ONLY the courses selected in the table on a card visual.. I have added the Medium Value onto a bar chart with the same filters and that works, however creating a DAX Measure using MEDIAN(Table[Student Numbers]) doesn't work, it retuns Value of 1.
Any ideas where i'm going wrong?
Solved! Go to Solution.
Hi @sarah2_williams ,
Please do this.
Median =
MEDIANX(
SUMMARIZE(
'Econ sample 2',
[2 HE Provider], [Course Title],
"__Sum FPE", SUM('Econ sample 2'[FPE])
),
[__Sum FPE]
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try this measure expression. It first makes a virtual table with the # of students in every selected combination of class and provider, and then takes the median of that new virtual column.
New Median =
VAR summary =
ADDCOLUMNS (
SUMMARIZE (
'Econ sample 2',
'Econ sample 2'[2 HE Provider],
'Econ sample 2'[Course Title]
),
"@students",
CALCULATE (
COUNT ( 'Econ sample 2'[Student] )
)
)
RETURN
MEDIANX (
summary,
[@students]
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@sarah2_williams ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @sarah2_williams ,
Please do this.
Median =
MEDIANX(
SUMMARIZE(
'Econ sample 2',
[2 HE Provider], [Course Title],
"__Sum FPE", SUM('Econ sample 2'[FPE])
),
[__Sum FPE]
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-lionel-msft Thanks!
Would the DAX be similar if I wanted to look for the upper and lower Percentiles?
Hi @amitchandak
Can you access the below:
https://1drv.ms/u/s!AgqFHfAlcE6nhD8ARKdbuX77wTJw?e=s5jEfW
This is a very basic example of how my data is, how I want to use filters in the report and how I want median to be displayed.
Thanks
HI,
Thank you for replying.. when trying to create a sample data set - which is much much smaller, it works fine. So I am a bit stuck, I will try another way to sed you the information
thanks
sarah
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 |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |