Sum Row by Row and Bin values into groups

[Anonymous]

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

[lukiz84 · Accepted solution]

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]

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

[Anonymous]

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!