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 need average loop type for the below table. loop_type column is a dimension. Thanks in advance.
Need to display this in a card. I have a project slicer to filter.
Project | ToolNo | loop_type | Count of loop_type |
PRJ-16 | 28-178 | Planned Loop | 2 |
PRJ-17 | 28-179 | Finished Loop | 4 |
PRJ-18 | 28-180 | Finished Loop | 6 |
PRJ-19 | 28-181 | Planned Loop | 1 |
PRJ-20 | 28-182 | Planned Loop | 1 |
Solved! Go to Solution.
Loop count = countx(filter(Table, [project] =earlier([project]) && [loop type] = earlier([loop type])),[loop type])
Avg Loop =AverageX(filter(Table, [project] =earlier([project])),[Loop count])
@harshagraj , not very clear
averagex(values(dim[Project]),max(Table[Count of loop_type]))
or
averagex(summarize(dim[Project],"_1",max(Table[Count of loop_type])),[_1])
Hi @amitchandak thanks for the reply. Actually i dont have [Count of loop_type] column i just changed that in value shelf from a table and dragged.
@harshagraj , Not very clear, what you need?
Sorry for the trouble. I have Project Number,Tool Number,Loop Type as columns.
In Loop Type column i have values as (Finished & Planned). Each tool number will have one loop type. So i need Averge loop type per project.
Eg.
PRJ-19 | 21-18 | Finished Loop |
PRJ-19 | 21-19 | Planned Loop |
So this has 1 Finished and 1 Planeed. So i need to display Average loop as 1.
@harshagraj , in case you need column. Both as new columns
Loop count = countx(filter(Table, [project] earlier([project]) && [loop type] = earlier([loop type])),[loop type])
Avg Loop =AverageX(filter(Table, [project] earlier([project])),[Loop count])
If a measure , refer - make is Avg or count
https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013
Hi @amitchandak thanks for the solution. But it is need working please correct the highligted error
countx(filter(Table, [project] earlier([project]) && [loop type] = earlier([loop type])),[loop type]).
Loop count = countx(filter(Table, [project] =earlier([project]) && [loop type] = earlier([loop type])),[loop type])
Avg Loop =AverageX(filter(Table, [project] =earlier([project])),[Loop count])
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |