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.
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?
Solved! Go to 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.
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.
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
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.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |