Help in building a template

[kjani]

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

[camargos88]

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

[kjani]

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.

[camargos88]

Hi @kjani ,

Check the file again, I've updated it.

Ricardo

[kjani]

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

[camargos88 · Accepted solution]

Hi @kjani ,

Check the code for the measure. It is subtracting the values and get the absolute value.

Ricardo