Sum only appearance per date and per truck

[Anonymous]

Hi guys

I'm trying to create a table to sum up the freight per project. The data base has duplicate projects per unit and I have created a measure to allocate the freight per project without considering the duplicates.

In the following exemple,  for the first unit "Truck 1" Date 2/1/24, Client 1 repeats 3 times, to calculate the allocation it was counted only once but it has a value anyways. For the table I'm trying to create I need to sum up the allocation only the first appearance per unit and per date.

Thanks

[123abc · Accepted solution]

To achieve the expected result in Power BI using DAX, you can create a new measure that calculates the sum of the Allocation only for the first appearance of each unit and project combination on a particular date. You can use the following DAX formula:

TotalAllocation =
SUMX(
SUMMARIZE(
'YourTableName',
'YourTableName'[Ship Date],
'YourTableName'[Unit],
'YourTableName'[Project],
"FirstAppearance", MIN('YourTableName'[Allocation])
),
[FirstAppearance]
)

Here's how the formula works:

1. SUMMARIZE: This function creates a summary table with unique combinations of Ship Date, Unit, and Project. For each combination, it calculates the minimum Allocation value (which represents the first appearance of that combination).

2. SUMX: This function iterates over each row of the summarized table and sums up the FirstAppearance values.

Make sure to replace 'YourTableName' with the actual name of your table containing the data.

Create a new measure using this DAX formula, and then use this measure in your Power BI report to display the total allocation per project, considering only the first appearance per unit and per date.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Thank you so much for taking the time to explain every step. It did resolve my issue!