cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AW1976NOVA
Post Patron
Post Patron

SSRS Nested SUM ( IIF Expression Need to Group By Date Please Help

I am new to SSRS and have a SUM(IIF question.

 

My data set contains four columns: Date, GroupID, PlanPaid, and NetworkIndicator.

 

Here is an example of the data set:

Capture.PNG

 

I am trying to SUM the [PlanPaid] amount when [NetworkIndicator] = "In Network".

 

However, I need this amount broken up by the [Date]. I tried accomplishing this by creating the expression:

=Sum(IIf(Fields!NetworkIndicator.Value = "In Network" , Fields!PlanPaid.Value , Nothing) , "Claims_Rolling12")

 

But this expression returns the same amount across all [Dates]:

Capture2.PNG

 

How do I break it up so that it is grouped by the correct [Date]?  I want it to return this:

Capture3.PNG

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Adding the "Claims_Rolling12" argument means that it's summing over that context instead of just the local context.

 

In this case, I think you can stick with just Sum(Fields!PlanPaid.Value) and put a NetworkIndicator filter on the entire Tablix.

AlexisOlson_0-1649275847507.png

View solution in original post

1 REPLY 1
AlexisOlson
Super User
Super User

Adding the "Claims_Rolling12" argument means that it's summing over that context instead of just the local context.

 

In this case, I think you can stick with just Sum(Fields!PlanPaid.Value) and put a NetworkIndicator filter on the entire Tablix.

AlexisOlson_0-1649275847507.png

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.