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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Akashi
Regular Visitor

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

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. 

 

Akashi_1-1683822951431.png

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). 

 

Akashi_2-1683824515557.png

 

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 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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]]])

vstephenmsft_1-1684226208874.png

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

vstephenmsft_0-1684226166029.png

                                                                                                                                                         

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.           

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

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]]])

vstephenmsft_1-1684226208874.png

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

vstephenmsft_0-1684226166029.png

                                                                                                                                                         

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.           

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors