cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Displaying Target Values versus Earnings Regardless of Earnings or Other Fields

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

AccountBranchDepartmentTransaction DateProduct TypePaymentBudget Key
S21MartianUp9/4/2016X$211.0009MartianUp2016
D11JovianTop8/11/2017Z$227.0008JovianTop2017
A20NeptunianTop5/13/2013Y$377.0005NeptunianTop2013
A21MartianDown7/31/2017X$288.0007MartianDown2017
F33JovianTop1/1/2014X$242.0001JovianTop2014
X13JovianDown6/5/2013Z$487.0006JovianDown2013
G56NeptunianDown9/30/2017Y$314.0009NeptunianDown2017
E83JovianTop4/9/2017X$378.0004JovianTop2017
A20MartianUp12/24/2011X$487.0012MartianUp2011
B01JovianDown4/6/2010Y$325.0004JovianDown2010
B02NeptunianTop3/28/2017Z$368.0003NeptunianTop2017
A20MartianUp5/23/2017Z$228.0005MartianUp2017
K00MartianTop10/8/2016Y$271.0010MartianTop2016
H41JovianDown2/29/2016X$418.0002JovianDown2016
B93NeptunianTop6/4/2017X$456.0006NeptunianTop2017
E40JovianTop5/10/2013Z$467.0005JovianTop2013

 

Budget

MonthBranchDepartmentYear Budget Budget Key
1MartianUp2017 $  945.0001MartianUp2017
2MartianUp2017 $  732.0002MartianUp2017
3MartianUp2017 $  447.0003MartianUp2017
4MartianUp2017 $  729.0004MartianUp2017
5MartianUp2017 $  625.0005MartianUp2017
6MartianUp2017 $  850.0006MartianUp2017
7MartianUp2017 $  159.0007MartianUp2017
8MartianUp2017 $  269.0008MartianUp2017
9MartianUp2017 $  401.0009MartianUp2017
10MartianUp2017 $  615.0010MartianUp2017
11MartianUp2017 $  653.0011MartianUp2017
12MartianUp2017 $  638.0012MartianUp2017
1MartianDown2017 $  114.0001MartianDown2017
2MartianDown2017 $  425.0002MartianDown2017
3MartianDown2017 $  297.0003MartianDown2017
4MartianDown2017 $  920.0004MartianDown2017
5MartianDown2017 $  954.0005MartianDown2017
6MartianDown2017 $  257.0006MartianDown2017
7MartianDown2017 $  507.0007MartianDown2017
8MartianDown2017 $  710.0008MartianDown2017
9MartianDown2017 $  106.0009MartianDown2017
10MartianDown2017 $  176.0010MartianDown2017
11MartianDown2017 $  484.0011MartianDown2017
12MartianDown2017 $  705.0012MartianDown2017
1MartianTop2017 $  379.0001MartianTop2017
2MartianTop2017 $  299.0002MartianTop2017
3MartianTop2017 $  992.0003MartianTop2017
4MartianTop2017 $  687.0004MartianTop2017
5MartianTop2017 $  762.0005MartianTop2017
6MartianTop2017 $  830.0006MartianTop2017
7MartianTop2017 $  429.0007MartianTop2017
8MartianTop2017 $  183.0008MartianTop2017
9MartianTop2017 $  876.0009MartianTop2017
10MartianTop2017 $  765.0010MartianTop2017
11MartianTop2017 $  921.0011MartianTop2017
12MartianTop2017 $  756.0012MartianTop2017
1JovianDown2017 $  982.0001JovianDown2017
2JovianDown2017 $  943.0002JovianDown2017
3JovianDown2017 $  786.0003JovianDown2017
4JovianDown2017 $  468.0004JovianDown2017
5JovianDown2017 $  437.0005JovianDown2017
6JovianDown2017 $  789.0006JovianDown2017
7JovianDown2017 $  299.0007JovianDown2017
8JovianDown2017 $  144.0008JovianDown2017
9JovianDown2017 $  729.0009JovianDown2017
10JovianDown2017 $  394.0010JovianDown2017
11JovianDown2017 $  600.0011JovianDown2017
12JovianDown2017 $  567.0012JovianDown2017
1JovianTop2017 $  943.0001JovianTop2017
2JovianTop2017 $  108.0002JovianTop2017
3JovianTop2017 $  931.0003JovianTop2017
4JovianTop2017 $  222.0004JovianTop2017
5JovianTop2017 $  586.0005JovianTop2017
6JovianTop2017 $  186.0006JovianTop2017
7JovianTop2017 $  396.0007JovianTop2017
8JovianTop2017 $  748.0008JovianTop2017
9JovianTop2017 $  781.0009JovianTop2017
10JovianTop2017 $  377.0010JovianTop2017
11JovianTop2017 $  390.0011JovianTop2017
12JovianTop2017 $  380.0012JovianTop2017
1NeptunianDown2017 $  955.0001NeptunianDown2017
2NeptunianDown2017 $  324.0002NeptunianDown2017
3NeptunianDown2017 $  371.0003NeptunianDown2017
4NeptunianDown2017 $  556.0004NeptunianDown2017
5NeptunianDown2017 $  285.0005NeptunianDown2017
6NeptunianDown2017 $  823.0006NeptunianDown2017
7NeptunianDown2017 $  786.0007NeptunianDown2017
8NeptunianDown2017 $  759.0008NeptunianDown2017
9NeptunianDown2017 $  177.0009NeptunianDown2017
10NeptunianDown2017 $  578.0010NeptunianDown2017
11NeptunianDown2017 $  191.0011NeptunianDown2017
12NeptunianDown2017 $  725.0012NeptunianDown2017
1NeptunianTop2017 $  192.0001NeptunianTop2017
2NeptunianTop2017 $  517.0002NeptunianTop2017
3NeptunianTop2017 $  163.0003NeptunianTop2017
4NeptunianTop2017 $  524.0004NeptunianTop2017
5NeptunianTop2017 $  346.0005NeptunianTop2017
6NeptunianTop2017 $  811.0006NeptunianTop2017
7NeptunianTop2017 $  129.0007NeptunianTop2017
8NeptunianTop2017 $  768.0008NeptunianTop2017
9NeptunianTop2017 $  768.0009NeptunianTop2017
10NeptunianTop2017 $  802.0010NeptunianTop2017
11NeptunianTop2017 $  979.0011NeptunianTop2017
12NeptunianTop2017 $  995.0012NeptunianTop2017


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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Displaying Target Values versus Earnings Regardless of Earnings or Other Fields

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.

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Displaying Target Values versus Earnings Regardless of Earnings or Other Fields

Hi @jmeccles,

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

1.PNG  2.PNG
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

Highlighted
Helper I
Helper I

Re: Displaying Target Values versus Earnings Regardless of Earnings or Other Fields

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.

Highlighted
Helper I
Helper I

Re: Displaying Target Values versus Earnings Regardless of Earnings or Other Fields

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.

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors