Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
xcaliverxx
New Member

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:

  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!

2 REPLIES 2
xcaliverxx
New Member

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 TypeProgramProvinceTxn IDTXN CoverageCalendar PeriodCalendar YearValue_RValue_IL 
AAAAAONC1BI9/30/201820181366205 
AAAAAONC1BI6/30/20192019-13503606 
AAAAAONC1AB7/31/20192019-13504844 
AEEEEONC2BI11/30/20202020-10002926 
AEEEEONC2AB12/31/20202020-10004307 
AEEEEONC2BI1/31/2022202216673521*note it skipped 2021 here to mimick the real data
AEEEEONC2AB1/31/2022202216671933 
BBBBBBCC3BI4/30/20182018-24384590 
BBBBBBCC3BI6/30/20192019-25004180 
BBBBBBCC3BI8/31/20202020-25002607 
BBBBBBCC3BI1/31/2021202174371436 
BBBBBBCC3BI8/31/2022202212432 
CCCCCQCC4AB1/31/2018201837524828 
CCCCCQCC4AB7/31/2019201914614826 
CCCCCQCC4AB3/31/2020202048914183 
CCCCCQCC4AB7/31/20212021-93141096 
CCCCCQCC4AB11/30/20222022940799 
DDDDDABC5AB6/30/2018201828364579 
DDDDDABC5AB2/28/20192019-65811330 
DDDDDABC5AB4/30/202020209303890 
DDDDDABC5AB4/30/20212021-66673321 
DDDDDABC5AB9/30/202220223610482 



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 PeriodCalendar YearCalendar Month  and so on
1/31/201820181    
2/28/201820182    
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 TypeProgramProvinceTxn IDTXN CoverageCalendar PeriodCalendar YearValue_R
AAAAAONC1BI9/30/201820181366
AAAAAONC1BI6/30/20192019-1350
AAAAAONC1AB7/31/20192019-1350
AEEEEONC2BI11/30/20202020-1000
AEEEEONC2AB12/31/20202020-1000
        
Txn IDCumulative Value_R    
C11334      
C22000      

 

3. If The cumulative Value_R > 0, then find the cumulative value_IL up to the previous year 
         
         
Data Source TypeProgramProvinceTxn IDTXN CoverageCalendar PeriodCalendar YearValue_RValue_IL
AAAAAONC1BI9/30/201820181366205
AAAAAONC1BI6/30/20192019-13503606
AAAAAONC1AB7/31/20192019-13504844
AEEEEONC2BI11/30/20202020-10002926
AEEEEONC2AB12/31/20202020-10004307
         
Txn IDCumulative Value_IL      
C18655       
C27233       



4. Desired Table    
     
     
Data Source TypeProgramProvinceTxn IDCumulative Value_IL
AAAAAONC18655
AEEEEONC27233
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
Community Support
Community Support

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors