Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

How to use DAX to aggregate deduplicated values?

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

BookingIDWorkOrderNumber
TotalAmountInvoiced
11100
21100
3250

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

1 ACCEPTED 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.

V-lianl-msft_0-1608712393286.png

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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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.

V-lianl-msft_0-1608712393286.png

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.

amitchandak
Super User
Super User

@Anonymous , Try a measure like

SUMX ( SUMMARIZE ( Service, Service[WorkOrderNumber] ,
"InvoicedAmount", AVERAGE ( [TotalAmountInvoiced] )), SUM ( [InvoicedAmount] ) )

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 

 

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
Not applicable

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?

 

EdCoffee_0-1608545767955.png

 

Hi @Anonymous ,

 

V-lianl-msft_0-1608620897225.png

 

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.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.