When you have detailed data mixed with summary data, you can’t directly using the simple aggregation function in PowerBI to do aggregation on the summary data as the results will not be correct (due to duplicate data). However, you can create a measure to lead PowerBI to do aggregation correctly.
For example, you have following data:
| Project Name | Allocated Hours | User Name | Used Hours |
|---|---|---|---|
| Project1 | 40 | User1 | 2 |
| Project1 | 40 | User2 | 4 |
| Project1 | 40 | User3 | 3 |
| Project2 | 100 | User1 | 20 |
| Project2 | 100 | User4 | 50 |
| Project2 | 100 | User3 | 10 |
When you need to report on Project Allocated Hours and Total Used Hours, the Allocated Hours can’t be simply sum up.
Create following meansure AllocatedHours and then report against the measure.
AllocatedHours =
11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111SUMX(
VALUES(SourceData[Project Name]),
CALCULATE(AVERAGE(SourceData[Allocated Hours]))
)
FEATURED TAGS
ai
api
automation
azure
azure cosmos db
azure openai
azure vm
base64
dax
docker
excel
execution plan
hyper-v
infrastructure
m
machine learning
machine learning services
network
network card
performance
power automate
power bi
power bi report tricks
power query
powershell
python
qgis
query performance
regex
replication
snowflake
sql
sql script
sql server
sql server admin
sql server config
ssl
ssms
troubleshooting
unicode
vmware