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 am trying to figure out how to calculate the following count and median for a data set with recurring rows. What would the measure syntax be to achieve the results on the bottom right of the image? Thanks in advance!
Solved! Go to Solution.
You can create a summarized table
SumTable = SUMMARIZE(Table2, Table2[ProjNbr], "Revenue", MAX(Table2[Revenue]))
For the count, you don't have to create the table explicitly:
Cnt = COUNTROWS ( SUMMARIZE ( Table2, Table2[ProjNbr], "Revenue", MAX ( Table2[Revenue] ) ) )
or
Cnt = COUNTROWS (SumTable)
But for the median I don't think you can avoid creating the table as MEDIAN requires a column name
Mdn = MEDIAN(SumTable[Revenue])
Hope this helps.
David
You can create a summarized table
SumTable = SUMMARIZE(Table2, Table2[ProjNbr], "Revenue", MAX(Table2[Revenue]))
For the count, you don't have to create the table explicitly:
Cnt = COUNTROWS ( SUMMARIZE ( Table2, Table2[ProjNbr], "Revenue", MAX ( Table2[Revenue] ) ) )
or
Cnt = COUNTROWS (SumTable)
But for the median I don't think you can avoid creating the table as MEDIAN requires a column name
Mdn = MEDIAN(SumTable[Revenue])
Hope this helps.
David
Thanks, David. That was extremely helpful!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |