cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tharris
Frequent Visitor

Tablix SUM of unique values

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.

 

tharris_0-1626730871482.png

tharris_1-1626730887314.png

 

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.

 

tharris_3-1626731302433.pngtharris_2-1626731238181.png

 

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. 

tharris_4-1626731761620.png

 

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.

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
tharris
Frequent Visitor

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

 

 

View solution in original post

3 REPLIES 3
tharris
Frequent Visitor

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

 

 

View solution in original post

yingyinr
Community Support
Community Support

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

yingyinr_0-1626943892932.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tharris
Frequent Visitor

Hi @yingyinr ,

 

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

tharris_0-1626973285485.png

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!

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors