A Measure or Process to consolidate unit of measures if there are duplicated IDs

[Akashi]

Hi Everyone,

I am pretty new to DAX measures and so I am looking for assistance on a measure or process to help me consolidate data for a $ (Spend)/ton (in this case Accessorial Spend per TN) analysis based on specific filtering of the data (by Vendor and by Per Diem here). I am using DAX measures with Power Pivot for this report. What you see below is the simple summary of the data, however, the problem lies in how the total tons is currently summed. 

Example Problem: 

Below is an example of the problem when we drill into the numbers based on the summary above. When filtering for a specific ID/BOL# in the details, 5083217 for example, you can see that because this raw data is by spend transactions there will be multiple instances of this BOL# being charged at different time period. Thus, I want to capture the total 'Spend' regardless time period, but because a specific BOL# will ALWAYS have the same tons, I only want one corresponding ton for a particular BOL# when the information is summarized above. Basically, the total tons should be 29 instead of 203 when filtering for this BOL# but the 203 will be apart of the summarized 54,432 total tons, which is overstating the total tons by 174 - assuming no other BOL# examples of this case in the detail (which is not the case). 

Impact of Problem to Summary Table (1st picture):

Overstatement of total tons which corresponds to a understatement of $ Spend/TN. 

Ending Question: 

Is there a measure that can exclude additional instances (transaction rows) after the first find of the Ton column to prevent overstating the Tons when all data is summarized?

P.S: My simple 'Ttl. Ton' measure is:

Ttl. Tons:=SUM(FreightAcc_Data[Tons])

If there is any additional information or support detail needed, please let me know. I appreciate any guidance to this issue!

Best Regards,

Justin 

[v-stephen-msft · Accepted solution]

Hi @Akashi ,

You can try the first measure below. The second measure is for comparative illustration.

Measure = var _count=CALCULATE(COUNT('Table'[FreightAcc_Data[BOL#]]]),FILTER(ALL('Table'),[FreightAcc_Data[BOL#]]]=MAX('Table'[FreightAcc_Data[BOL#]]])))
var _sum=CALCULATE(SUM('Table'[FreightAcc_Data[Tons]]]),FILTER(ALL('Table'),[FreightAcc_Data[BOL#]]]=MAX('Table'[FreightAcc_Data[BOL#]]])))
return DIVIDE(_sum,_count)

Measure 2 = SUM('Table'[FreightAcc_Data[Tons]]])

After the index column is removed, the first measure is still 29.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.