Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello folks,
I have the following scenario:
Have sales goal monitored through a Table Visual, showing:
SalesMan | Goal | Sales |
Leon Customer1 Customer2 Customer3 |
1000 500 800 |
100 200 600 |
Mark Customer5 Customer9 Customer10 |
1000 500 800 |
100 200 600 |
I have the following tables:
SalesGoalTable
Date | CustomerID | CustomerName | Activity | SalesGoal | SalesMan |
10/01/2019 | 123456 | John C. | Retail | 100 | Mark |
10/01/2019 | 645123 | Mr. Beginner | Retail | 200 | Leon |
10/01/2019 | 789456 | Mr. CantFigure | Wholesale | 500 | Leon |
10/01/2019 | 978456 | Ms. Pretty | Retail | 500 | Mark |
SalesTable
Date | CustomerID | CustomerName | Activity | Sales | SalesMan |
10/05/2019 | 123456 | John C. | Retail | 120 | Mark |
10/09/2019 | 005689 | Mr. Falker | Retail | 50 | Mark |
10/10/2019 | 789456 | Mr. CantFigure | Wholesale | 200 | Leon |
10/25/2019 | 963852 | Mr. Efraim | Retail | 700 | Leon |
The report is filtered by Date (from a calendar table created) and by Activity - also independently created.
I have appended these two tables, so that I can have a list of the customers who bought and the ones who had goals set, but didn't show up in the Sales Fact Table, but when putting them on a table visual, I get Grand Total Sales and the SalesGoal Column contains no value.
Hope it's enough information to get a light towards the solution.
Thanks a lot for any help.
Hi @Asantos2020
In Edit queries,
in "goal table", add a column by merging [SalesMan], [CustomerID], [Activity], [Date] columns.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjC0VNJRMjQyNjE1AzK88jPyFJz1gKyg1JLEzByQnIEBkPRNLMpWitVB1WZmYgrUCZIt0lNwSk3PzMtLLULWawTW65Oan4eh19zCEmIlSK9zYl6JW2Z6aVEqUCA8Iz8ntTgxB8Q2xW2ApbkF1IBiPYWAotSSkkpkq02RnB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, CustomerID = _t, CustomerName = _t, Activity = _t, SalesGoal = _t, SalesMan = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"CustomerID", Int64.Type}, {"CustomerName", type text}, {"Activity", type text}, {"SalesGoal", Int64.Type}, {"SalesMan", type text}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "link1", each Text.Combine({[SalesMan], Text.From([CustomerID], "en-US"), [Activity], Text.From([Date], "en-US")}, "__"), type text)
in
#"Inserted Merged Column"
in "fact table", add a date column which shows start date per month,
then merge columns [SalesMan], [CustomerID], [Activity], [Start of Month]
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9U3MjC0VNJRMjQyNjE1AzK88jPyFJz1gKyg1JLEzBywnAGQ9E0sylaK1QFrs4RpMzWzAFG+RXoKbok52alFyPpM0bUBEVSfuYUlxDqQTufEvBK3zPTSolSgQHhGfk5qcWIOiG1kADLBJzU/D2aCEdzBlmbGFqZGUBNc04oSM3OR7TZH0hoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, CustomerID = _t, CustomerName = _t, Activity = _t, Sales = _t, SalesMan = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"CustomerID", Int64.Type}, {"CustomerName", type text}, {"Activity", type text}, {"Sales", Int64.Type}, {"SalesMan", type text}}),
#"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted Start of Month", "link2", each Text.Combine({[SalesMan], Text.From([CustomerID], "en-US"), [Activity], Text.From([Start of Month], "en-US")}, "__"), type text)
in
#"Inserted Merged Column"
Close&&apply, return to data model
Create relationships
The data in this example is not completed, so you see blank rows above.
Hi @Anonymous !
I'll check the answer below to see if this solves the problem. If not, I'll be more than happy to provide more info.
Thank you for your time.
Cheers,
Antonio
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |