Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@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 !
Proud to be a PBI Community Champion
Hi, how will I do that when it is in 2 different rows? 🙂
@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")
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |