Help: Typical Wrong Total Sum, Correct Rows

[xcaliverxx]

Hello,

After days of trial and error, I still couldn't figure this out. Either I'm extremely dumb, and Power BI is the death of total values

Goal

users can select different years in a filter, and obviously, this will need to be reflected in this measure:

1. sum abs(round(table2[R],2)) if in table2 with a filter on table2[Calendar_Year] smaller than user-selected Calendar_year (Ex. if a user selects 2018 in the slicer, sum table2[R] up to 2017) -> hence this is a cumulative value

2. if the cumulative value > 0, then sum table2[Net IL] for all table2[Calendar_Year] smaller than user-selected Calendar_year

Current DAX

PYE =

var _cumulativeR = abs(round(Calculate(SUM(Table2[R]), Table2[CALENDAR_YEAR] < SELECTEDVALUE(CalendarPeriod[CALENDAR_YEAR]), all(CalendarPeriod[CALENDAR_YEAR]),all(Table2[ClaimsFeature])),2))

var _cumulativeIL = Calculate(SUM(Table2[Net IL]), Table2[CALENDAR_YEAR] < SELECTEDVALUE(CalendarPeriod[CALENDAR_YEAR]), all(CalendarPeriod[CALENDAR_YEAR]))

return if(_cumulativeR > 0, _cumulativeIL)

Problem

The row values are all correct, and if I export the data and the measure in Excel, even the total is correct. The total sum displayed in PBI is outrageously high.

FYI, suppose Table 2 has an ID number as an identifier. It's possible one ID could have transactions in, say 2017, and no txns in 2018, and 1 txn in 2019.
If the user selects 2018, normally, this ID would not show up in the visual table, but I want it to appear if it still has some cumulative R. I suspect this is part of the problem.

I've looked into SUMX(Summary()) and stuff, but I could not get it to work.col4, but I can't get the correct total for just col1.

I've looked into SUMX(Summary()) and stuff, but I could not get it to work.

if SUMX is the way to go, how can I achieve the same thing? 😞 
Please send help. Thanks!

[xcaliverxx]

Fake Dataset
*Users should be able to filter via slicers on data source type, program, province,  TXN ID, TXN Coverage, And Calendar Year
Data Source Type	Program	Province	Txn ID	TXN Coverage	Calendar Period	Calendar Year	Value_R	Value_IL
A	AAAA	ON	C1	BI	9/30/2018	2018	1366	205
A	AAAA	ON	C1	BI	6/30/2019	2019	-1350	3606
A	AAAA	ON	C1	AB	7/31/2019	2019	-1350	4844
A	EEEE	ON	C2	BI	11/30/2020	2020	-1000	2926
A	EEEE	ON	C2	AB	12/31/2020	2020	-1000	4307
A	EEEE	ON	C2	BI	1/31/2022	2022	1667	3521	*note it skipped 2021 here to mimick the real data
A	EEEE	ON	C2	AB	1/31/2022	2022	1667	1933
B	BBBB	BC	C3	BI	4/30/2018	2018	-2438	4590
B	BBBB	BC	C3	BI	6/30/2019	2019	-2500	4180
B	BBBB	BC	C3	BI	8/31/2020	2020	-2500	2607
B	BBBB	BC	C3	BI	1/31/2021	2021	7437	1436
B	BBBB	BC	C3	BI	8/31/2022	2022	1	2432
C	CCCC	QC	C4	AB	1/31/2018	2018	3752	4828
C	CCCC	QC	C4	AB	7/31/2019	2019	1461	4826
C	CCCC	QC	C4	AB	3/31/2020	2020	4891	4183
C	CCCC	QC	C4	AB	7/31/2021	2021	-9314	1096
C	CCCC	QC	C4	AB	11/30/2022	2022	940	799
D	DDDD	AB	C5	AB	6/30/2018	2018	2836	4579
D	DDDD	AB	C5	AB	2/28/2019	2019	-6581	1330
D	DDDD	AB	C5	AB	4/30/2020	2020	930	3890
D	DDDD	AB	C5	AB	4/30/2021	2021	-6667	3321
D	DDDD	AB	C5	AB	9/30/2022	2022	3610	482

1. Make a new table in PBI called Calendar Period, ranging from 2018/1/1 ~ 2022/12/31.
This is so if the user selects on data source system = A, the Calendar_Year Slicer still shows 2021.
Connect to the Fake Dataset by Calendar_Period
Calendar Period	Calendar Year	Calendar Month			and so on
1/31/2018	2018	1
2/28/2018	2018	2

2. Suppose the user selects datasource = A & Calendar Year = 2021
For each Txn ID, find the cumulative value_R up to the previous year, aka 2020, and round it to 2 digits, and apply abs()
Data Source Type	Program	Province	Txn ID	TXN Coverage	Calendar Period	Calendar Year	Value_R
A	AAAA	ON	C1	BI	9/30/2018	2018	1366
A	AAAA	ON	C1	BI	6/30/2019	2019	-1350
A	AAAA	ON	C1	AB	7/31/2019	2019	-1350
A	EEEE	ON	C2	BI	11/30/2020	2020	-1000
A	EEEE	ON	C2	AB	12/31/2020	2020	-1000
Txn ID	Cumulative Value_R
C1	1334
C2	2000

3. If The cumulative Value_R > 0, then find the cumulative value_IL up to the previous year
Data Source Type	Program	Province	Txn ID	TXN Coverage	Calendar Period	Calendar Year	Value_R	Value_IL
A	AAAA	ON	C1	BI	9/30/2018	2018	1366	205
A	AAAA	ON	C1	BI	6/30/2019	2019	-1350	3606
A	AAAA	ON	C1	AB	7/31/2019	2019	-1350	4844
A	EEEE	ON	C2	BI	11/30/2020	2020	-1000	2926
A	EEEE	ON	C2	AB	12/31/2020	2020	-1000	4307
Txn ID	Cumulative Value_IL
C1	8655
C2	7233

4. Desired Table
Data Source Type	Program	Province	Txn ID	Cumulative Value_IL
A	AAAA	ON	C1	8655
A	EEEE	ON	C2	7233
Total				15888
*Problem: Everything is fine until the Total; it has something do with using IF statement on my end… need a revision to show the sum correctly (the row values are fine)

The column names my DAX uses may slightly be different.

Thanks!

[v-junyant-msft]

Hi @xcaliverxx ,

Can I ask you to provide sample data? If you are concerned about security, please replace your original data with the test data and provide it to me, preferably with your desired results (results chart). It's hard to understand what your needs are and find out what your problems are based on words alone.

Best Regards,
Dino Tao