How can I remove duplicate values from a single column with multiple year refrenced

[mdrammeh]

I have a dataset with millions of records for multiple “CD_WR”(Column). For each CD_WR, I want to count the “Total Miles” (Column D) once to avoid duplicating the total number of miles. How should I approach this in Power BI or Power Query?

How can I count the miles once? Or divide by the count of miles?

[v-huizhn-msft]

Hi @mdrammeh,

The picture displays the expected result, right? If it is, in Power Query Editor, please select the CD_WR and DT_BUDGET_YR columns, right click->remove the duplicate value, it will remove the duplicates according to CD_WR and DT_BUDGET_YR columns. For instance, I select the Date and area fields->remove duplicates, it will return the expected result shown in second screenshot.

After remove the duplicate, click the Apply&Close, you will get the distinct tables, then you create a calclated column using the formula below.

Sum of Total miles=CALCULATE(SUM(Table[Total Miles]),ALLEXCEPT(Table, Table[CD_WR]))

If this is not what you want, please post the sample data for further analysis.

Best Regards,
Angelia

[mdrammeh]

Hi Angelia,

So when you look at the Sum of Miles column at the very end, notice that the same miles are duplicated for each year. What I want to do is to either average out the sub-totals so that each year represents a portion of the miles or make it such that the miles will only be counted once. 

For example, let's say you have 9 miles of construction work to be completed in three years (2016, 2017, and 2018). For each year, you want to show that only 3 miles of work have been constructed. But your data shows that 9 miles for each year of work completed. How would you organize your data such that it would only show the average miles of the total (9 miles) to be spread out for each year in the order shown above? 

Thank!

Mustapha

[MattAllington]

You haven't shared your data structure so it is hard to give you a precise answer. However looking at the output you posted, I could guess at the following. 

Write these measure

Total miles = Sum(table[total miles])

total years = calculate(distinctcount(table[cd_wr]),all(table[budget year]))

Avg Miles = divide([total miles],[total years])

the secret sauce is that total years always returns the total regardless of a budget year filter. 

Here is some dreading for you 

http://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/

http://exceleratorbi.com.au/create-lookup-table-power-pivot/

[mdrammeh]

Hi Angelia,

So when you look at the Sum of Miles column at the very end, notice that the same miles are duplicated for each year. What I want to do is to either average out the sub-totals so that each year represents a portion of the miles or make it such that the miles will only be counted once. 

For example, let's say you have 9 miles of construction work to be completed in three years (2016, 2017, and 2018). For each year, you want to show that only 3 miles of work have been constructed. But your data shows that 9 miles for each year of work completed. How would you organize your data such that it would only show the average miles of the total (9 miles) to be spread out for each year in the order shown above? 

Thank!

Mustapha