write a condition in SSRS as specified

Question

I am trying to find out how can I write the below logic in SSRS:

The structure of my report is:

All the rows and columns are grouped together.

The New Week Status has 2 fields: Current Week and Previous Week.

Current Week ranges from 30th Jan - 5th Feb

Previous Week ranges from 23th Jan - 29th Feb

The Gross Sales is in %.

I need to display only those restaurants where at least 5 days in Current Week and Previous Week is above 2% Gross.

Example:

In the above example, R1 has more than 5 days > 2%, so it will be displayed

R2 has less than 5 days > 2%, so it won't be displayed

How can the logic be implemented for this?

Answer 1

If you are not calculating the Gross Sales in the report this could work for you.

Select the whole Restaurant row and right click it, select Row Visibility... option:

Select Show or hide based on an expression: and use this expression:

=IIF(SUM(IIF(Fields!Gross_Sales.Value> 0.02,1,0))>=5,False,True)

Now only rows with at least 5 dates that have more than 0.02 (2%) Gross Sales are visible.

UPDATE: Adding examples.

I've recreated your dataset and the matrix, so having this structure:

Using the expression I posted above it hides the R2 row as expected.

UPDATE:

=IIF(
  SUM(IIF(Fields!Total_Cost.Value/Fields!Sales_Gross.Value>0.02,1,0))>=5
  or
  SUM(IIF(Fields!New_Week_Status.Value = "Current Week" and Fields!Total_Cost.Value/Fields!Sales_Gross.Value>0.02,1,0))>=3
,False,True)