Forum Discussion
Traffic Light Report
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?
1 Reply
- 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.
