Forum Discussion
Tablix SUM of unique values
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))
Hi Anonymous ,
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
Hi Anonymous ,
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!