Hi all
I'm hoping someone can help me. I'm trying to create a query in Power Bi with visualisations etc. I have an organisation list, which my colleagues update regularly to add new organisations and also update the organisations' relationship type. Each month I download a copy, to capture what each organisation's relationship type was at a given month and I have pasted this into an excel spreadsheet as per the below dummy data (example 1.). I want to use this data as the source in PowerBI.
I want my query to allow me to select two given months and compare the changes in relationship types and show me the number and percentage of organisations to have improved, declined or remained static in terms of their relationship type between those two given periods (example 2).
I also want to be able to show that the number and percentage of organisations in a given month to be at each relationship type. e.g. in May there were 2 organisations out of 9 (22%) labelled as "neutral" and in June this changed to 3 (33%). In May there were 33% of orgs labelled as "Critic" and in July there was 30% - bearing in mind there was also an additional organisation added. Similar to (example 3)
I'm at a total loss over how to achieve this in Power BI. Can anyone help me please?
1. Raw data:
2. Example of showing improvement between July and Aug - I achieved it in Excel, but not in PowerBI - also Excel won't allow me to choose the two periods I want to compare. I had to do formulas to calculate it based the two dates I had chosen.
3. Example of the tables showing percentages of organisations at each relationship level
I'm hoping someone can help me. I'm trying to create a query in Power Bi with visualisations etc. I have an organisation list, which my colleagues update regularly to add new organisations and also update the organisations' relationship type. Each month I download a copy, to capture what each organisation's relationship type was at a given month and I have pasted this into an excel spreadsheet as per the below dummy data (example 1.). I want to use this data as the source in PowerBI.
I want my query to allow me to select two given months and compare the changes in relationship types and show me the number and percentage of organisations to have improved, declined or remained static in terms of their relationship type between those two given periods (example 2).
I also want to be able to show that the number and percentage of organisations in a given month to be at each relationship type. e.g. in May there were 2 organisations out of 9 (22%) labelled as "neutral" and in June this changed to 3 (33%). In May there were 33% of orgs labelled as "Critic" and in July there was 30% - bearing in mind there was also an additional organisation added. Similar to (example 3)
I'm at a total loss over how to achieve this in Power BI. Can anyone help me please?
1. Raw data:
| Org ID | Organisation Name | Relationship type | Score (calculated based on relationship type) | Date |
| 1 | Costco | Neutral | 2 | 31/05/2023 |
| 2 | WH Smith | 0 | 31/05/2023 | |
| 3 | Starbucks | Critic | 1 | 31/05/2023 |
| 4 | Wilkos | Interested | 3 | 31/05/2023 |
| 5 | Pizza Hut | 0 | 31/05/2023 | |
| 6 | McDonald's | 0 | 31/05/2023 | |
| 7 | KFC | Critic | 1 | 31/05/2023 |
| 8 | Boots | Neutral | 2 | 31/05/2023 |
| 9 | Argos | Critic | 1 | 31/05/2023 |
| 1 | Costco | Neutral | 2 | 30/06/2023 |
| 2 | WH Smith | Critic | 1 | 30/06/2023 |
| 3 | Starbucks | Critic | 1 | 30/06/2023 |
| 4 | Wilkos | Interested | 3 | 30/06/2023 |
| 5 | Pizza Hut | Neutral | 2 | 30/06/2023 |
| 6 | McDonald's | Critic | 1 | 30/06/2023 |
| 7 | KFC | Critic | 1 | 30/06/2023 |
| 8 | Boots | Neutral | 2 | 30/06/2023 |
| 9 | Argos | Critic | 1 | 30/06/2023 |
| 1 | Costco | Critic | 1 | 30/07/2023 |
| 2 | WH Smith | Critic | 1 | 30/07/2023 |
| 3 | Starbucks | Interested | 3 | 30/07/2023 |
| 4 | Wilkos | Neutral | 2 | 30/07/2023 |
| 5 | Pizza Hut | Neutral | 2 | 30/07/2023 |
| 6 | McDonald's | Neutral | 2 | 30/07/2023 |
| 7 | KFC | Neutral | 2 | 30/07/2023 |
| 8 | Boots | Interested | 3 | 30/07/2023 |
| 9 | Argos | Neutral | 2 | 30/07/2023 |
| 10 | River Island (new org) | Critic | 1 | 30/07/2023 |
| 1 | Costco | Neutral | 2 | 31/08/2023 |
| 2 | WH Smith | Neutral | 2 | 31/08/2023 |
| 3 | Starbucks | Partner | 5 | 31/08/2023 |
| 4 | Wilkos | Interested | 3 | 31/08/2023 |
| 5 | Pizza Hut | Interested | 3 | 31/08/2023 |
| 6 | McDonald's | Engaged | 4 | 31/08/2023 |
| 7 | KFC | Neutral | 2 | 31/08/2023 |
| 8 | Boots | Neutral | 2 | 31/08/2023 |
| 9 | Argos | Interested | 3 | 31/08/2023 |
| 10 | River Island (new org) | Neutral | 2 | 31/08/2023 |
2. Example of showing improvement between July and Aug - I achieved it in Excel, but not in PowerBI - also Excel won't allow me to choose the two periods I want to compare. I had to do formulas to calculate it based the two dates I had chosen.
| Comparing July and August | ||
| Row Labels | Number of Organisations | Percentage of Organisations |
| Declined | 1 | 10% |
| Improved | 8 | 80% |
| Static | 1 | 10% |
| Grand Total | 10 | 100% |
3. Example of the tables showing percentages of organisations at each relationship level
| May | 9 orgs | ||||
| Partner | Engaged | Interested | Neutral | Critic | No data |
| 0% | 0% | 11% | 22% | 33% | 33% |
| June | 9 orgs | ||||
| Partner | Engaged | Interested | Neutral | Critic | No data |
| 0% | 0% | 11% | 33% | 56% | 0% |
| July | 10 Orgs | ||||
| Partner | Engaged | Interested | Neutral | Critic | No data |
| 0% | 0% | 20% | 50% | 30% | 0% |
| August | 10 Orgs | ||||
| Partner | Engaged | Interested | Neutral | Critic | No data |
| 10% | 10% | 30% | 50% | 0% | 0% |
