Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi @kjani ,
Check the code for the measure. It is subtracting the values and get the absolute value.
Ricardo
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.
I am sorry, it's still adding the numbers. I need to see the difference in values between two scenarios (1_Base vs. 2_Low). Can you help? Thanks
Hi @kjani ,
Check the code for the measure. It is subtracting the values and get the absolute value.
Ricardo
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |