topic Sum Row by Row and Bin values into groups in DAX Commands and Tips

Sum Row by Row and Bin values into groups

Anonymous — Wed, 12 Oct 2022 05:29:16 GMT

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

Re: Sum Row by Row and Bin values into groups

lukiz84 — Wed, 12 Oct 2022 05:35:32 GMT

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

Re: Sum Row by Row and Bin values into groups

Anonymous — Wed, 12 Oct 2022 07:23:35 GMT

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!

Re: Sum Row by Row and Bin values into groups

lukiz84 — Wed, 12 Oct 2022 14:50:38 GMT

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