Hello
I am looking for a little help as I am trying to develop a dashboard.
I want to create a measure of revenue per customer – and the wrinkle is that I have an entity and a number of sub-entities.
The overall entity attracts the customers overall and the sub-entities get the revenue. So I need to be able to match-up the two. I also want to be able to see the total entity revenue per customer as well as the revenue per customer for each sub-entity. I have various scenarios – Budget, Actual and Last Year and the Months and Years so I need to be able to see each separately
I have set out the tables that I have below and here are a couple of examples of the
Entity A
Total January Budget Revenue Per Customer = (25+12)/116 = 0.31896
Entity A – Sub-entity 1
Total January Actual Revenue Per Customer = (36)/587 = 0.061328
Any help gratefully appreciated…
Sub-entity Table
Entity Code | Sub-Entity Code | Month | Total Revenue | Year | Scenarios |
A | 1 | January | 25 | 2023 | Budget |
A | 2 | January | 12 | 2023 | Budget |
B | 1 | January | 61 | 2023 | Budget |
B | 2 | January | 7 | 2023 | Budget |
A | 1 | January | 36 | 2023 | Actual |
A | 2 | January | 12 | 2023 | Actual |
B | 1 | January | 61 | 2023 | Actual |
B | 2 | January | 7 | 2023 | Actual |
A | 1 | January | 62 | 2023 | Actual |
A | 2 | January | 79 | 2023 | Actual |
B | 1 | January | 53 | 2023 | Actual |
B | 2 | January | 99 | 2023 | Actual |
A | 1 | February | 81 | 2022 | Last Year |
A | 2 | February | 55 | 2022 | Last Year |
B | 1 | February | 96 | 2022 | Last Year |
B | 2 | February | 43 | 2022 | Last Year |
Entity Table
Entity Code | Month | Number of Customers | Year | Scenarios |
A | January | 116 | 2023 | Budget |
B | January | 500 | 2023 | Budget |
A | January | 587 | 2023 | Actual |
B | January | 505 | 2023 | Actual |
A | February | 552 | 2022 | Last Year |
B | February | 651 | 2022 | Last Year |
