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 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
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 |