Displaying Target Values versus Earnings Regardless of Earnings or Other Fields

[jmeccles]

Hello, everyone!

Here's a problem that I feel should be relatively easy to solve but.. it's giving me a hard time so.. help me, please?

Now, ..I've got two (2) tables: An "Earnings" table (with non-unique entries) and a "Budget" table (with unique entries) that are as follows:

Earnings

Account	Branch	Department	Transaction Date	Product Type	Payment	Budget Key
S21	Martian	Up	9/4/2016	X	$211.00	09MartianUp2016
D11	Jovian	Top	8/11/2017	Z	$227.00	08JovianTop2017
A20	Neptunian	Top	5/13/2013	Y	$377.00	05NeptunianTop2013
A21	Martian	Down	7/31/2017	X	$288.00	07MartianDown2017
F33	Jovian	Top	1/1/2014	X	$242.00	01JovianTop2014
X13	Jovian	Down	6/5/2013	Z	$487.00	06JovianDown2013
G56	Neptunian	Down	9/30/2017	Y	$314.00	09NeptunianDown2017
E83	Jovian	Top	4/9/2017	X	$378.00	04JovianTop2017
A20	Martian	Up	12/24/2011	X	$487.00	12MartianUp2011
B01	Jovian	Down	4/6/2010	Y	$325.00	04JovianDown2010
B02	Neptunian	Top	3/28/2017	Z	$368.00	03NeptunianTop2017
A20	Martian	Up	5/23/2017	Z	$228.00	05MartianUp2017
K00	Martian	Top	10/8/2016	Y	$271.00	10MartianTop2016
H41	Jovian	Down	2/29/2016	X	$418.00	02JovianDown2016
B93	Neptunian	Top	6/4/2017	X	$456.00	06NeptunianTop2017
E40	Jovian	Top	5/10/2013	Z	$467.00	05JovianTop2013

Budget

Month	Branch	Department	Year	Budget	Budget Key
1	Martian	Up	2017	$  945.00	01MartianUp2017
2	Martian	Up	2017	$  732.00	02MartianUp2017
3	Martian	Up	2017	$  447.00	03MartianUp2017
4	Martian	Up	2017	$  729.00	04MartianUp2017
5	Martian	Up	2017	$  625.00	05MartianUp2017
6	Martian	Up	2017	$  850.00	06MartianUp2017
7	Martian	Up	2017	$  159.00	07MartianUp2017
8	Martian	Up	2017	$  269.00	08MartianUp2017
9	Martian	Up	2017	$  401.00	09MartianUp2017
10	Martian	Up	2017	$  615.00	10MartianUp2017
11	Martian	Up	2017	$  653.00	11MartianUp2017
12	Martian	Up	2017	$  638.00	12MartianUp2017
1	Martian	Down	2017	$  114.00	01MartianDown2017
2	Martian	Down	2017	$  425.00	02MartianDown2017
3	Martian	Down	2017	$  297.00	03MartianDown2017
4	Martian	Down	2017	$  920.00	04MartianDown2017
5	Martian	Down	2017	$  954.00	05MartianDown2017
6	Martian	Down	2017	$  257.00	06MartianDown2017
7	Martian	Down	2017	$  507.00	07MartianDown2017
8	Martian	Down	2017	$  710.00	08MartianDown2017
9	Martian	Down	2017	$  106.00	09MartianDown2017
10	Martian	Down	2017	$  176.00	10MartianDown2017
11	Martian	Down	2017	$  484.00	11MartianDown2017
12	Martian	Down	2017	$  705.00	12MartianDown2017
1	Martian	Top	2017	$  379.00	01MartianTop2017
2	Martian	Top	2017	$  299.00	02MartianTop2017
3	Martian	Top	2017	$  992.00	03MartianTop2017
4	Martian	Top	2017	$  687.00	04MartianTop2017
5	Martian	Top	2017	$  762.00	05MartianTop2017
6	Martian	Top	2017	$  830.00	06MartianTop2017
7	Martian	Top	2017	$  429.00	07MartianTop2017
8	Martian	Top	2017	$  183.00	08MartianTop2017
9	Martian	Top	2017	$  876.00	09MartianTop2017
10	Martian	Top	2017	$  765.00	10MartianTop2017
11	Martian	Top	2017	$  921.00	11MartianTop2017
12	Martian	Top	2017	$  756.00	12MartianTop2017
1	Jovian	Down	2017	$  982.00	01JovianDown2017
2	Jovian	Down	2017	$  943.00	02JovianDown2017
3	Jovian	Down	2017	$  786.00	03JovianDown2017
4	Jovian	Down	2017	$  468.00	04JovianDown2017
5	Jovian	Down	2017	$  437.00	05JovianDown2017
6	Jovian	Down	2017	$  789.00	06JovianDown2017
7	Jovian	Down	2017	$  299.00	07JovianDown2017
8	Jovian	Down	2017	$  144.00	08JovianDown2017
9	Jovian	Down	2017	$  729.00	09JovianDown2017
10	Jovian	Down	2017	$  394.00	10JovianDown2017
11	Jovian	Down	2017	$  600.00	11JovianDown2017
12	Jovian	Down	2017	$  567.00	12JovianDown2017
1	Jovian	Top	2017	$  943.00	01JovianTop2017
2	Jovian	Top	2017	$  108.00	02JovianTop2017
3	Jovian	Top	2017	$  931.00	03JovianTop2017
4	Jovian	Top	2017	$  222.00	04JovianTop2017
5	Jovian	Top	2017	$  586.00	05JovianTop2017
6	Jovian	Top	2017	$  186.00	06JovianTop2017
7	Jovian	Top	2017	$  396.00	07JovianTop2017
8	Jovian	Top	2017	$  748.00	08JovianTop2017
9	Jovian	Top	2017	$  781.00	09JovianTop2017
10	Jovian	Top	2017	$  377.00	10JovianTop2017
11	Jovian	Top	2017	$  390.00	11JovianTop2017
12	Jovian	Top	2017	$  380.00	12JovianTop2017
1	Neptunian	Down	2017	$  955.00	01NeptunianDown2017
2	Neptunian	Down	2017	$  324.00	02NeptunianDown2017
3	Neptunian	Down	2017	$  371.00	03NeptunianDown2017
4	Neptunian	Down	2017	$  556.00	04NeptunianDown2017
5	Neptunian	Down	2017	$  285.00	05NeptunianDown2017
6	Neptunian	Down	2017	$  823.00	06NeptunianDown2017
7	Neptunian	Down	2017	$  786.00	07NeptunianDown2017
8	Neptunian	Down	2017	$  759.00	08NeptunianDown2017
9	Neptunian	Down	2017	$  177.00	09NeptunianDown2017
10	Neptunian	Down	2017	$  578.00	10NeptunianDown2017
11	Neptunian	Down	2017	$  191.00	11NeptunianDown2017
12	Neptunian	Down	2017	$  725.00	12NeptunianDown2017
1	Neptunian	Top	2017	$  192.00	01NeptunianTop2017
2	Neptunian	Top	2017	$  517.00	02NeptunianTop2017
3	Neptunian	Top	2017	$  163.00	03NeptunianTop2017
4	Neptunian	Top	2017	$  524.00	04NeptunianTop2017
5	Neptunian	Top	2017	$  346.00	05NeptunianTop2017
6	Neptunian	Top	2017	$  811.00	06NeptunianTop2017
7	Neptunian	Top	2017	$  129.00	07NeptunianTop2017
8	Neptunian	Top	2017	$  768.00	08NeptunianTop2017
9	Neptunian	Top	2017	$  768.00	09NeptunianTop2017
10	Neptunian	Top	2017	$  802.00	10NeptunianTop2017
11	Neptunian	Top	2017	$  979.00	11NeptunianTop2017
12	Neptunian	Top	2017	$  995.00	12NeptunianTop2017

Now, "Earnings" represents some sales values and "Budget" is the set of target values for the "Earnings".

Interestingly, a "Budget" value can be specific to a "Month", "Branch", "Department" and "Year" but there's no specific budget value for "Product Type". I decided to link both tables with a "Budget Key" which is just the concatenation of "Month", "Branch", "Department" and "Year" (this may be the problem).

Now, ..I want to create a report using either a gauge or a line and clustered column chart with slicers for "Month", "Branch", "Department", "Year" and "Product Type" in which the target value (for the gauge) and the line value (for the line and clustered) would be the sum of "Budget" and the callout value (gauge) or column value (line and clustered) would represent the sum of "Payment".

That's pretty standard stuff until you try slicing by either A) a month in which there are no corresponding "Payment" values or B) slicing by "Product Type" for which there's no corresponding "Budget". For (A), even if there are no payments recorded for a selected month, a target value/"Budget" exists and should be displayed on the gauge/line. For (B), if there isn't a budget set for a slicer's field, this slicer should only affect the callout values/columns and not the target values.

I'm pretty sure it's my understanding of relationships in Power BI that's making this seem difficult for me so maybe you can help.

Any ideas?

[jmeccles · Accepted solution]

I figured it out. The Budget Key was problematic. I created separate tables for Department, Branch (each with unique rows) and a calendar containing the year and month.

To make it all work, I linked both the Earnings and Budget tables indirectly to each other via the Department, Branch and calendar tables (all "single" cross filter direction). Everything else fell into place.

Thanks.

[v-huizhn-msft]

Hi @jmeccles,

After research, you need to create a line and clustered column chart like the following screenshot.

 
You said you create slicers for "Month", "Branch", "Department", "Year" and "Product Type". But what they are used for? And in the line and clustered chart, the x-axis displays the month "Month", "Branch", "Department" and so on? You want the slicer affect them?

Best Regards,
Angelia

[jmeccles]

Hi, @v-huizhn-msft! Thank you for responding.

I already went about creating both the line and clustered chart and the gauge chart, as you've done. The slicers are intended to view the "Payment" contributed by the respective departments, branches, months or years and their relation to the designated budgets.

To be a bit more specific, the target line on the gauge would represent the budget as selected by slicers for the "Month", "Department", "Branch" or "Year" since they are all fields in the "Budget" table. But there's no budget for "Product Type" in the "Budget" table so I don't want its slicer to ever affect the target line; I just want its slicer to affect the columns or gauge value to show the earnings contributed by the selected "Product Type".

Using the example I gave:

(1) If I set the slicers { "Product Type" = "All" }, { "Department" = "Down" }, { "Branch" = "Neptunian" }, { "Month" = "9" }, { "Year" = "2017" }, the corresponding callout value on the gauge (i.e. the "Payment") will read $314.00 and the target line will read $177.00 which is the corresponding "Budget" for Neptunian's Down department in September 2017.

(2) If I set the slicers { "Product Type" = "X" }, { "Department" = "Down" }, { "Branch" = "Neptunian" }, { "Month" = "9" }, { "Year" = "2017" }, the corresponding callout value on the gauge will read $0.00 (since there were no payments for X that correspond to the other criteria) but the target line will read $177.00 which is the corresponding "Budget" for Neptunian's Down department in September 2017.

(3) If I set the slicers { "Product Type" = "X" }, { "Department" = "Top" }, { "Branch" = "Jovian" }, { "Month" = "All" }, { "Year" = "2017" }, the corresponding callout value on the gauge will read $378.00 and the target line will read $6,048.00 which is the corresponding "Budget" for Jovian's Top department in 2017 - unaffected by the product type.

The line of the line and clustered chart would be analogous to the target line of the gauge; the columns of the line and clustered chart would be analogous to the callout value of the gauge.

I hope this helps.