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.
I am looking for a way where my total average for a category, becomes my target for a specifit task and added as a new column using a meausement in my Matrix Table.
In my matrix table i have the tasks, with an IDs and Time (Avg) it took to complete a project. The Time is base on another average from a table (Time Measure is just basic Average, "Time=Calculate(Average(Table1[Time]))" )
I am looking to add the "Target (Average Per Task)" column, which is my task Average, so i need it to repeat for each ID. The measurements i have used to accoumplish this have provide me the same value as my Time Average.
What I have Tried
Target Avg = AVERAGEX(VALUES(Table1[Task]),CALCULATE(AVERAGE(Table1.Time])))
Target Avg = CALCULATE(AVERAGE(Table1[Time])AllSELECTED(Table1.[Task])
Target Avg = CALCULATE(AVERAGE(Table1[Time]))
Task | Average Per Task |
1-2 | 6.33 |
2-3 | 4.33 |
3-4 | 6.00 |
Task | ID | Time | Target (Average Per Task) |
1-2 | M100 | 5 | 6.33 |
1-2 | M200 | 4 | 6.33 |
1-2 | M300 | 10 | 6.33 |
1-2 | M301 | 5 | 6.33 |
2-3 | M100 | 4 | 4.33 |
2-3 | M125 | 3 | 4.33 |
2-3 | M301 | 6 | 4.33 |
3-4 | M100 | 8 | 6 |
3-4 | M125 | 4 | 6 |
Any Assistance will be greatly appreciated!!! Thanks,
Solved! Go to Solution.
Hi @Anonymous ,
Try this measure:
Thank you for quick response. It did work with some changes that i made. The measurement you provided does make the number static for each task, but it takes the whole average in my table, ignoring any filters i have in my visual. What i did i added the rest of the filter to the ALLEXECEPT Calculation. Unless there is a different way to include them.
Avg = CALCULATE(AVERAGE('Table'[Time]), ALLEXCEPT('Table';'Table'[Task],'Table';'Table'[Date],'Table';'Table'[Program],'Table';'Table'[Task]'Table';'Table'[Type]))
Thanks Again for quick response. I am new to PBI and still learning
@Anonymous ,
You should include the columns on ALLEXCEPT functions that are part of a combination for an exception. If you miss at least 1 column, it will replicate until the combination is done.
Glad it helped you.
Ricardo
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |