Traffic Light Report

[joan]

I need to create a report that shows dispensed medication in a table looking as follows for a particular company:

I have the following filters on the report:

-          Date

-          Product

-          Name

The report will be pre-filtered to a company.

To calculate the status field, I have created a metric as follows:

Status = divide(sum(Table[Current Period]),sum(Table[Last Period]),0)

To display the icon I have created the following column (as an Image URL):

Status Icon = if([Status]>1.05,Table[Image Green Circle],if([Status]<0.95,Table[Image Red Circle],Table[Image Amber Circle]))

The Image fields are simply columns with a link to the icons on our server.

My raw data looks something like this (dummy data):

Company	Name	Drug	Current Period	Last Period	Month
Company A	John Smith	ATORVASTATIN	545	595	April 2017
Company A	John Smith	IRBESARTAN	545	540	April 2017
Company A	Kath Jones	PARACETAMOL	540	478	April 2017
Company A	Joe Nguyen	PARACETAMOL	539	463	April 2017
Company A	Kath Jones	PARACETAMOL	538	506	February 2017
Company A	Kath Jones	PARACETAMOL	535	443	March 2017
Company A	Kath Jones	ATORVASTATIN	530	449	April 2017
Company A	John Smith	PARACETAMOL	513	446	May 2017
Company B	Jack Hill	PARACETAMOL	490	446	April 2017
Company B	Thomas Lee	PARACETAMOL	486	446	July 2017
Company B	Peter Johnson	PARACETAMOL	485	446	May 2017
Company B	Peter Johnson	ATORVASTATIN	482	595	April 2017

The problem is that the status icon calculation does not work at a rolled-up level.

On the ‘Status Icon’ field I can choose ‘Don’t summarize’, but then I see several rows for a product whereas I want to see the total row for a product. Selecting ‘First’ or ‘Last’ will give me the wrong icon colour.

Creating a summary table will not work either as I need to be able to filter down to a detailed level, e.g. down to name in this example.

What can I do to display the correct colour icon at a summary as well as a detailed level in the same table?

[GilbertQ]

What I think you would need to do, is to ensure that when your data is rolled up or down that the calculation reflects this.

What I would typically do is I would create a table as you have done, then put in my measure. Then see if it is having the correct outcome first and the lower level. And then roll it up and see if it has the correct outcome when rolled up?
I have found that in the past it is incorrect when getting rolled up. And often what I would do to resolve this would be is to Divide my measure, but the COUNTROWS, so that when it is on the lower level detail it would be the [Status]\1, but on a rolled up level it would be [Status] \ Number of rows being represented.