Forum Discussion
Help in building a template
I would like your help in building this in PowerBI Desktop. I have a dataset with multiple variables. I need to calculate difference in values based on scenario (e.g. 1_Base vs. 2_Low) for each Name (UC, GR etc.). Here is sample input data. Can you help with a simplest desgin in PowerBI? I would like to have output mostly in tables (examples below) and a few charts on what changed.
I will have more than 2 scenarios in future, so would like to have flexibility to compare one scenario vs. other. Thanks in advance.
Input data:
| Revenue Center | CAC Code | CAC Desc | Channel Code | Channel Desc | Model Type | Rank | Rank Name | Scenario | Name | Value |
| Central Zone | 5 | Large Store | 50 | Channel A | Non 500 | 50 | Channel A | 1_Base | UC | 62 |
| Central Zone | 5 | Large Store | 50 | Channel A | Non 500 | 50 | Channel A | 1_Base | GR | 242 |
| Central Zone | 5 | Large Store | 30 | Channel B | Top 500 | 51 | Channel B | 1_Base | UC | 14 |
| Central Zone | 5 | Large Store | 30 | Channel B | Top 500 | 51 | Channel B | 1_Base | GR | 23 |
| Central Zone | 5 | Large Store | 10 | Channel C | Non 500 | 52 | Channel C | 1_Base | UC | 5 |
| Central Zone | 5 | Large Store | 10 | Channel C | Non 500 | 52 | Channel C | 1_Base | GR | 38 |
| Central Zone | 5 | Large Store | 20 | Channel D | Top 500 | 53 | Channel D | 1_Base | UC | 162 |
| Central Zone | 5 | Large Store | 20 | Channel D | Top 500 | 53 | Channel D | 1_Base | GR | 252 |
| East Zone | 5 | Small Store | 50 | Channel A | Non 500 | 50 | Channel A | 1_Base | UC | 62 |
| East Zone | 5 | Small Store | 50 | Channel A | Non 500 | 50 | Channel A | 1_Base | GR | 242 |
| East Zone | 5 | Small Store | 30 | Channel B | Top 500 | 51 | Channel B | 1_Base | UC | 14 |
| East Zone | 5 | Small Store | 30 | Channel B | Top 500 | 51 | Channel B | 1_Base | GR | 23 |
| East Zone | 5 | Small Store | 10 | Channel C | Non 500 | 52 | Channel C | 1_Base | UC | 5 |
| East Zone | 5 | Small Store | 10 | Channel C | Non 500 | 52 | Channel C | 1_Base | GR | 38 |
| East Zone | 5 | Small Store | 20 | Channel D | Top 500 | 53 | Channel D | 1_Base | UC | 162 |
| East Zone | 5 | Small Store | 20 | Channel D | Top 500 | 53 | Channel D | 1_Base | GR | 252 |
| Central Zone | 5 | Large Store | 50 | Channel A | Non 500 | 50 | Channel A | 2_Low | UC | 56 |
| Central Zone | 5 | Large Store | 50 | Channel A | Non 500 | 50 | Channel A | 2_Low | GR | 218 |
| Central Zone | 5 | Large Store | 30 | Channel B | Top 500 | 51 | Channel B | 2_Low | UC | 13 |
| Central Zone | 5 | Large Store | 30 | Channel B | Top 500 | 51 | Channel B | 2_Low | GR | 20 |
| Central Zone | 5 | Large Store | 10 | Channel C | Non 500 | 52 | Channel C | 2_Low | UC | 5 |
| Central Zone | 5 | Large Store | 10 | Channel C | Non 500 | 52 | Channel C | 2_Low | GR | 35 |
| Central Zone | 5 | Large Store | 20 | Channel D | Top 500 | 53 | Channel D | 2_Low | UC | 146 |
| Central Zone | 5 | Large Store | 20 | Channel D | Top 500 | 53 | Channel D | 2_Low | GR | 227 |
| East Zone | 5 | Small Store | 50 | Channel A | Non 500 | 50 | Channel A | 2_Low | UC | 56 |
| East Zone | 5 | Small Store | 50 | Channel A | Non 500 | 50 | Channel A | 2_Low | GR | 218 |
| East Zone | 5 | Small Store | 30 | Channel B | Top 500 | 51 | Channel B | 2_Low | UC | 13 |
| East Zone | 5 | Small Store | 30 | Channel B | Top 500 | 51 | Channel B | 2_Low | GR | 20 |
| East Zone | 5 | Small Store | 10 | Channel C | Non 500 | 52 | Channel C | 2_Low | UC | 5 |
| East Zone | 5 | Small Store | 10 | Channel C | Non 500 | 52 | Channel C | 2_Low | GR | 35 |
| East Zone | 5 | Small Store | 20 | Channel D | Top 500 | 53 | Channel D | 2_Low | UC | 146 |
| East Zone | 5 | Small Store | 20 | Channel D | Top 500 | 53 | Channel D | 2_Low | GR | 227 |
Output calculations:
| 1_Base vs. 2_Low variance | ||
| By Channel | UC | GR |
| Channel A | ||
| Channel B | ||
| Channel C | ||
| Channel D | ||
| Total |
| 1_Base vs. 2_Low variance | ||
| By Revenue Center | UC | GR |
| Central Zone | ||
| East Zone | ||
| Total |
| 1_Base vs. 2_Low variance | ||
| By Model Type | UC | GR |
| Top 500 | ||
| Non 500 | ||
| Total |
Hi kjani ,
Check the code for the measure. It is subtracting the values and get the absolute value.
Ricardo
5 Replies
Hi kjani ,
You just need to drag a matrix visualization to show it:
Check this file: Download PBIX
If you consider it as a solution, please mark as a solution and kudos.
Ricardo
Thank you so much for quick response. However, I need the difference between the two scenarios. Your output is adding instead of subtracting. Thanks again.
