Forum Discussion
Help: Typical Wrong Total Sum, Correct Rows
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:
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
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!
2 Replies
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 TaoFake 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!
