Forum Discussion

jaynesa's avatar
jaynesa
Icon for Helper II rankHelper II
6 years ago

Calculating a total while ignoring duplicate values in a column

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

Need Help

10 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      jaynesa
      Icon for Helper II rankHelper II
      6 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      jaynesa
      Icon for Helper II rankHelper II
      6 years ago

      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?

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      jaynesa
      Icon for Helper II rankHelper II
      6 years ago

      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's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        6 years ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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.