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 "Proj Aggregation Test" table in powerBI.

 

 

However, when I create a table in PowerBI, and put the financials, it is giving me the TOTAL value instead of the sum of each line item (eg. first row should be the total financials of the 2 projects in that bin)

 

How do I write a DAX to sum only the rows???

 

 

 

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
       )
    )      
)

3 Replies

  • 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!
  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    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
       )
    )      
)