Tablix SUM of unique values

[tharris]

Hello All,

I have created a Paginated report in Report Builder that contains several value columns and 2 types of rows that are grouped together as below.

The 2 fields at the end of my table contain values that are related to the project name level, but not for the account description level, hence why I left those cells balnk. The issue I am having is on my Sums for Contract and Funded values, the sum is working as if the value on the project name repeats under the account desciption, as it is the nature of the data in a flat table, and my sums are incorrect. The tables below help visualize what I am referring to, on the left is what I show on my report results, and how I need my sum to calculate(sum one one instance per project name), based on the values under project name only; on the right table is what my sum is actually calculating - because this is how the flat table works in the dataset.

I have searched a lot for what fucntion I can use to sum only the values under project name, ie, instead of summing those values 4 times plus the next 4 for the next project name, it will only sum each one time, regardless of how many acct descriptions are under each project name. Some have 4, other have 1, others 6. There is no constant for me to be able to just divide at the end. I am yet to find something that will work. the sum of ReportItems returned me a error. 

I have tried to split the data where those 2 columns come from another "Header" level table and adding those fileds as a Lookup, but unfortunately, that did  not work either, I just got the same sum result as if those tables were being joined together to create another table...similar to joining fields of this nature in SQL.

Does anyone know howI can make this work?

Thank you very much in advance! I have been stuck on this for over a week now and have run out of ideas to try.

[tharris · Accepted solution]

Adding the rownumber to a new column to count the number of accts under each project name did the trick for me. Once I added the new column to the dataset, in the backend in SQL, since I couldn't figure out in DAX, I was able to get my Sums to work perfectly using the formula below.

=SUM(IIF(Fields!group_row_number.Value = 1, Fields!contract_value_total_amount.Value, 0))

[v-yiruan-msft]

Hi @tharris ,

What's your expected result? Whether the below screenshot is what you want? Could you please provide the related Value expression when you get the error message? By the way, what's the expression of field [Contract Value Price]?

Best Regards

[tharris]

Hi @v-yiruan-msft ,

My expected result is for the sum of the column, as it shows here, to sum only the values being shown on the report vs all that is in the database.

For example, in the screenshot I posted of what my data is looking like, I have this

This is what I want and would expect to see for my table elements, however, my sum, instead of gving me $88,359, which is the sum of those 2 numbers, it is giving me $353,436. It is summing the $44k 4 times for the 4 rows under the project name and the same with the $43k. The $43k and $44k are not the sum of the numbers on the detail rows, as it doesn't apply to the rows as this belongs at the header level. 

That error message is what I got when I tried to do a Sum(ReportItems!conrtact_value_total_amount). Now I know that you cannot aggregate Report Items. This is unfortunate. 

The expression is just =Fields!contract_value_total_amount.Value and =Sum(Fields!contract_value_total_amount.Value) for the sum.

The results I am getting under project name are correct. My only issue is with the sum. 

Right now I am adding a rownumber partition by to my dataset to then try to sum when the row number =1, but have not been able to find anything else that could make this work.

In Power BI Desktop, I was able to create a measure to calculate this properly, but unfirtunately, I cannot apply it to Report Builder, 1 because I can't use DAX on an expression, 2 because I cannot convert it to visual basic as it lacks some functionality.

This is my DAX formula:

Contract Value Price = SUMX(DISTINCT('reporting progress_payments_billing_monthly'[project_name]),CALCULATE(AVERAGE('reporting progress_payments_billing_monthly'[contract_value_total_amount])))

Thanks!