DAX to Remove Column Duplicates and Deliver Expected SUM for Total for a Column

[Anonymous]

Community,

I am in need of help on removing a duplicate row to deliver the correct calculation I am seeking. Any help with a DAX solution to this much appreciated. I have provided a sample of the issues below as well as a sample file.

In this sample I have 3 tables, they are;

Weightings, CarConfig, and ForceData

In the table below I have added columns representing elements of the 3 tables. The data has been filtered by "TestID" and "Weighted ID"

Also included in the table is a measure created to calculate the "Weighted Value" of the "Data" for each "Attitude ID".

The measure is:

Weighted Value =

var item1 = SUM(ForceData[Data])

var item2 = MIN(Weightings[Weighting])

return

item1 * item2

In this example I would like to have the duplicate of the "Attitude ID" (in this case attitude ID 1) removed before the calculations are completed.

Also, I would like for the the "Weighted Value" to show the SUM of that column (this should be 16).

Below is a link to the PBIX file.

PBIX FILE

Thanks for any assistance offered!!

[sturlaws]

Hi @Anonymous,

[Attitude ID] = 1 occurs twice in your table because there are two different RunIDs for this [Attitude ID], 1 and 5, for your filter selection([TestID]=1,00 and [Weighted ID]=1). If you remove RunID from your visual, [Attitude ID] = 1 is no longer repeated. If this is not what you are looking for, could please state how you want to handle different RundIDs

cheers

[sturlaws · Accepted solution]

In order to easily identify rows which should be removed, create a new calculated column like this

repeatTestFlag =
IF (
    CarConfig[RunID]
        = CALCULATE (
            MAX ( CarConfig[RunID] );
            FILTER ( CarConfig; CarConfig[TestID] = EARLIER ( CarConfig[TestID] ) )
        );
    1;
    0
)

Then change the Weighted Value-measure to this:

Weighted Value =
SUMX (
    CarConfig;
    CALCULATE ( SUM ( ForceData[Data] ); CarConfig[repeatTestFlag] = 0 )
        * CALCULATE ( MIN ( Weightings[Weighting] ); CarConfig[repeatTestFlag] = 0 )
)

This removes the repeated row from the calculation, if you want to remove it from the table as well, add the column repeatTestFlag to the visual filter-pane of the visual and remove the rows where repeatTestFlag=1

[Anonymous]

Thanks!! This will work