Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
How could you use DAX to create a measure that aggregates deduplicated values from a fact table?
Specfically I have a table - Service - which is created by joining Bookings with WorkOrders. A WorkOrder can have 0 to many Bookings and there is an attribute of a WorkOrder - TotalAmountInvoiced - that we include in Service, but because the granularity of the table is at the Booking level the TotalAmountInvoiced can be duplicated, as shown below:
Service Table
BookingID | WorkOrderNumber | TotalAmountInvoiced |
1 | 1 | 100 |
2 | 1 | 100 |
3 | 2 | 50 |
So where WorkOrderNumber =1 the TotalAmountInvoiced is duplicated
I have tried various ways of creating a table variable that changes the granularity of the table to the WorkOrderNumber (best effoert below) but the results are always. Any ideas where we're going wrong?
Total Amount Invoiced =
VAR WorkOrder =
ADDCOLUMNS (
SUMMARIZE ( Service, Service[WorkOrderNumber] ),
"InvoicedAmount", AVERAGE ( [TotalAmountInvoiced] )
)
RETURN
SUMX ( WorkOrder, SUM ( [InvoicedAmount] ) )
NB for the sake of simplicty lets assume we must use a measure to solve this problem and can't remodel this table in any way
Solved! Go to Solution.
Hi @Anonymous ,
DAX measure are based on row context so any given value from the visualizations can influence your result.
Although you define a table in measure, when you put the values into the visual, they will also be relevant to the current context of the fact table.
Workorderid is repeated in the table. When you use the summarize function to aggregate the average value based on the workorderid, the value obtained is based on the average value of workorderid. If you use the sum function, the average value of all workorderids will be summed in the visual.
So you can't use sum function here, just use this column directly or use average function.
You can also refer to these blogs for row context:
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
https://www.statslab-bi.co.nz/project/row-filter-context-dax/
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @V-lianl-msft that is in effect what I'm doing with measure 2. My question remains though; why is this necessary? The average AvgAmount is 100 on each row so an avergae of that should be 100
Hi @Anonymous ,
DAX measure are based on row context so any given value from the visualizations can influence your result.
Although you define a table in measure, when you put the values into the visual, they will also be relevant to the current context of the fact table.
Workorderid is repeated in the table. When you use the summarize function to aggregate the average value based on the workorderid, the value obtained is based on the average value of workorderid. If you use the sum function, the average value of all workorderids will be summed in the visual.
So you can't use sum function here, just use this column directly or use average function.
You can also refer to these blogs for row context:
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
https://www.statslab-bi.co.nz/project/row-filter-context-dax/
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try a measure like
SUMX ( SUMMARIZE ( Service, Service[WorkOrderNumber] ,
"InvoicedAmount", AVERAGE ( [TotalAmountInvoiced] )), SUM ( [InvoicedAmount] ) )
Hi @amitchandak unfortuantely not. That's very similar to the meausre I have and it isn't working
Hi @Anonymous ,
Could you provide a sample pbix to help us more clear about the problem?
Please remove any sensitive data before uploading.
If you are not sure how to provide sample data, please refer to this blog:
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @V-lianl-msft I have created a sample file to analyse here https://drive.google.com/drive/folders/1uAFseQMLD5842Gnhvu3KrQ8BJHugs9jt
I've managed to create a measure that works (by dividing the result or a measure similar to the one provided by @amitchandak by a count of rows) but I'm keen to understand why this is necessary?
Hi @Anonymous ,
Change SUM( [AvgAmount] ) to AVERAGE ( [AvgAmount] ).
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.