Filter away row if another row exist with a value

[PeterStuhr]

Hello all,

I have the following table:

FinancialDetailRevenue	FinancialDetailType	FinancialDetailDate
18000	KPI	1. december 2019
18000	KPI	1. november 2019
16000	KPI	1. oktober 2019
24000	KPI	1. september 2019
10000	KPI	1. august 2019
12000	KPI	1. juli 2019
18000	KPI	1. juni 2019
14000	KPI	1. maj 2019
14000	KPI	1. april 2019
18000	KPI	1. marts 2019
20000	KPI	1. februar 2019
18000	KPI	1. januar 2019
10000	Budget	1. december 2019
20000	Budget	1. november 2019
15000	Budget	1. oktober 2019
15000	Budget	1. september 2019
20000	Budget	1. august 2019
15000	Budget	1. juli 2019
20000	Budget	1. juni 2019
15000	Budget	1. maj 2019
20000	Budget	1. april 2019
20000	Budget	1. marts 2019
15000	Budget	1. februar 2019
15000	Budget	1. januar 2019

This is our "budget" table. Normally, an account will have either 12 Budget lines( each month) or 12 KPI lines (Each month). Budget Lines are for our Existing customers, and KPI lines are usually for our New customers. We use these against "actual sales" numbers to see if they are trending alright. The issue for us is that for SOME accounts, we have both 12 budget lines and 12 KPI lines (used for different purposes for some big accounts). So my issue is, that for those, it will sum double for me.

I want to be able to SUM the FinancialDetailRevenue column. But it should not sum it all, if there are both Budget and KPI lines. If that is the case, it should always sum the Budget only.

So it should be like: Sum FinancialDetailRevenue lines, but if there are both Budget and KPI lines (1 of each per month), then only sum the budget ones.

Is this possible at all?

THanks!

[PattemManohar]

@PeterStuhr  Yes, it's possible. You can flag the accounts where are there both KPI and Budget using a calculated column for the KPI records as "N" and then filter those records from your SUM calculation. Please try and let me know if you got stuck !

[PeterStuhr]

Hi, how will I do that when it is in 2 different rows? 🙂

[PattemManohar]

@PeterStuhr  Please try this as a new column

Flag = 
VAR _Cnt = CALCULATE(DISTINCTCOUNT(TestLkp[Type]),FILTER(TestLkp,TestLkp[AcctID]=EARLIER(TestLkp[AcctID])))
RETURN IF(_Cnt>1,IF(TestLkp[Type]="KPI","N","Y"),"Y")