SUM only the values contained in a separate column

[toddpbi]

Hi there everyone,

Here is my data set(s):

What I am trying to achieve is to sum up the total actuals column only if the values contained in column "cost report" are present in "GL account" in table one. What is the formula required to acheive this total? Essentially, I want to only aggregate the sub-accounts without double counting the main accounts (represented by *, ** etc).

Thank you for your help.

[OwenAuger]

Hi there,

It's best not to include total rows of any sort in a fact table, so that this double-counting problem doesn't arise.

I would recommend you remove any rows where Cost Report contains a "*" by modifying the original query.

You should then ensure there is a relationship between the Cost report column in the 2nd table and the GL Account column in the 1st table, and ensure that any required hierarchy columns are present in the 1st table (looks like they probably are).

Regards,

Owen

[toddpbi · Accepted solution]

Dear Owen, Thank you very much for your help.

That was my original plan of attack to solve this issue. But by removing the rows containing the "*" astericks, it would require a manual adjustment when new accounts are added to the Power BI file.

Insead, what I did was create a temporary "vlookup column", which searches whether the GL account from the 2nd table contains values from the 1st hierarchy table using this formula:

VLOOKUP = CALCULATE(COUNTROWS('Chart of Accounts'), FILTER('Chart of Accounts', 'Chart of Accounts'[GL Account]=Actuals[GL Account])) > 0

This returns true or false values depending on whether the account name is found in the hierarchy GL column from the hierarchy table. Then I created a measure: Total Actuals = CALCULATE ( SUM ( 'Actuals[Actuals]), Actuals[VLOOKUP] = TRUE() to avoid the double counting issue.

Thank you again for your help!!