Calculating a total while ignoring duplicate values in a column

[jaynesa]

I'm currently loading and transforming my table "Payroll" into Power Query.  And I want to be able calculate a true annual total for the Payroll, without double counting.  The data load comes out like this:

How the data is being brought into Power Query

I want to be able to calculate a true annual total for 2019, resulting in Q1+Q2+Q3+Q4, or rather 5000+7500+4000+6000 = 22500.  How can I achieve this in the column "Annual Payroll Total"?  I've been racking my brain on this all  night, please help!!!

What I ultimately want to be able to do

[Ashish_Mathur]

Hi,

Drag the first 4 fields to a Table visual and write these measures

Average location payroll = average(Data[Total location payroll])

Annual Payroll total = SUMX(Data,[Average location payroll])

Hope this helps.

[v-yingjl]

Hi @jaynesa ,

I have added some rows data about 2020 like this:

Create the following measure:

Annual Payroll Total =
SUMX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Payroll' ),
            'Payroll'[Organization],
            'Payroll'[Location],
            'Payroll'[Category],
            'Payroll'[Year],
            'Payroll'[Qtr],
            "Annual Payroll Total", MAX ( 'Payroll'[Total Location Payroll] )
        ),
        [Year] = SELECTEDVALUE ( Payroll[Year] )
    ),
    [Annual Payroll Total]
)

You will get your expected output:

Here is the sample file that hopes to help you, please try it: Calculating a total while ignoring duplicate values in a column.pbix 

Best Regards,
Yingjie Li

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

[jaynesa]

This is extremely good... but why can't I get it to work in a column?  And only a measure?  I really need it in the column.  Any suggestions?

[v-yingjl]

Hi @jaynesa ,

If you want to create a calculated column, you can try this formula:

Total =
VAR tab =
    FILTER (
        SUMMARIZE (
            ALL ( 'Payroll' ),
            'Payroll'[Organization],
            'Payroll'[Location],
            'Payroll'[Category],
            'Payroll'[Year],
            'Payroll'[Qtr],
            'Payroll'[Total Location Payroll]
        ),
        [Year] = EARLIER ( Payroll[Year] )
    )
RETURN
    SUMX ( tab, [Total Location Payroll] )

You will get the expected result like this:

Best Regards,
Yingjie Li

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

[jaynesa]

It worked!!  THANKYOU!!!

[jaynesa]

Yingjie Li, I'm almost there.  Question, in the Section that you have:  [Year] = Earlier (Payroll[Year]) 

Can I add more filters than just the year?  For example, if I wanted to have both Year and Location, what would that syntex be?  I've tried a few times but it keeps telling me that the syntax is wrong.  

[AiolosZhao]

Hi @jaynesa ,

Please try to use the below measure:

KEY = 'Table'[Ora] & 'Table'[Loc] & 'Table'[CAT] & 'Table'[Year] & 'Table'[QTR]

Measure 3 = SUMX(SUMMARIZE(ALL('Table'),'Table'[KEY],"annual",DIVIDE(SUM('Table'[payroll]),COUNTA('Table'[KEY]),0)),[annual])

KEY is a calculated column.

Aiolos Zhao

[jaynesa]

This is getting me close.  So, let's say the year changes, or some other column like "Category".  For exampe, if the year changes to 2020, how can I make sure the new column calc only calc's 2020?  How will it know to not include 2019?

[amitchandak]

@jaynesa 

Try something like this

sumx(Summarize(Table,Table[organization],table[location], Table[Category] ,Table[year],Table[Qtr],"_1",max(Table[Total location Payroll])),[_1])

[jaynesa]

So, let's say the year changes, or some other column like "Category".  For exampe, if the year changes to 2020, how can I make sure the new column calc only calc's 2020?  How will it know to not include 2019?