Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sum Row by Row and Bin values into groups

Hi, I have a raw data set where there are multiple lines for each Project ID and Country. I then grouped this data by Project ID & Country (Market), and aggregated all NTSLC$ values. This is my "Pro...
DAX
sum row by row
sumx
  • lukiz84's avatar
    lukiz84
    3 years ago
    NTSLC SUMS =
VAR selectedRangeMin = SELECTEDVALUE('Proj Value Rank'[MinValue Range])
VAR selectedRangeMax = SELECTEDVALUE('Proj Value Rank'[MaxValue Range])
IF (
    HASONEVALUE ( 'Proj Value Rank'[MinValue Range] ),
    CALCULATE (
       SUM ( 'Proj Aggregation Test'[Sum NTSLC] ),
       FILTER('Proj Aggregation Test',
          'Proj Aggregation Test'[Sum NTSLC] => selectedRangeMin &&
          'Proj Aggregation Test'[Sum NTSLC] < selectedRangeMax
       )
    )      
)
lukiz84's avatar
lukiz84
Icon for Memorable Member rankMemorable Member
3 years ago

Where is the Value Range Column and how is it connected to your data?

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi lukiz84 ,

My Value Range column is from a separate table called "Proj Value Rank" (not connected to any tables):

 

 

I used this message to group projects into bins using this link: 

https://www.burningsuit.co.uk/blog/dax-how-group-measures-numeric-ranges

 

This is my DAX measure based on the above link:

No of Projects = IF(HASONEVALUE('Proj Value Rank'[MinValue Range]), COUNTROWS(FILTER('Proj Aggregation Test', [Sum NTSLC]>=VALUES('Proj Value Rank'[MinValue Range])&& [Sum NTSLC]<values('Proj Value Rank'[MaxValue Range]) ) ), COUNTROWS('Proj Aggregation Test') )
 
THANK YOU!