Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi guys!
I'm struggling with one thing in my reports. I have a fact tables with data per each year-month. Now I've been able to show also info of another fact table. This one comes with the granularity of year, and the formula to get the objective per month is as simple as divide per 12.
What I'm trying to achieve with 0 success is to generate a table that taking the original table, generates a new one multiplying all the records while dividing the values and acumulating them over those months.
The original table is like this:
CountryId | TypeId | Year | SalesObjective | ClientObjective |
ES | 2 | 2020 | 12 | 24 |
UK | 3 | 2020 | 36 | 12 |
US | 3 | 2020 | 24 | 72 |
I need to generate this:
Im totally lost on this. I don't mind to generate it either in power query or in dax, I'm not been able to do it in any of them... but if I had to choose the preference is power query.
Thanks and regards!
Solved! Go to Solution.
Hi @Borja204 ,
What I did was to create a new table with the years / months:
Added a new column to this table with the name of the table with the sales objectives:
SalesObjectives
Expanded the new column
Then added two custom columns:
[Custom.SalesObjective]/12*[Month]
[Custom.ClientObjective]/12*[Month]
Deleted the previous two columns and final result below:
Check full code for the second table below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6pEQAwCATAXk4jgPy1MPTfRqIyh1y3EXB1hcCQ8uGMxuiMwZiMxdiMwzAtKgd7ibw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each SalesObjectives),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "TypeID", "Year", "SalesObjective", "ClientObjective"}, {"Custom.Country", "Custom.TypeID", "Custom.Year", "Custom.SalesObjective", "Custom.ClientObjective"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Salesobjective", each [Custom.SalesObjective]/12*[Month]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "ClientObjective", each [Custom.ClientObjective]/12*[Month]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom.SalesObjective", "Custom.ClientObjective"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.Country", Order.Ascending}, {"Year", Order.Ascending}, {"Month", Order.Ascending}})
in
#"Sorted Rows"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêslet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg1W0lEyAmEDIwMgZQhmmyjF6kQrhXoD2cYIOWMziAKwXDCqHFCLjpI5UC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountryId = _t, TypeId = _t, Year = _t, SalesObjective = _t, ClientObjective = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CountryId", type text}, {"TypeId", Int64.Type}, {"Year", Int64.Type}, {"SalesObjective", Int64.Type}, {"ClientObjective", Int64.Type}}),
#"Added Custom" = Table.RemoveColumns(Table.AddColumn(#"Changed Type", "Custom", each Table.FromRows(List.Accumulate({1..12}, {}, (s,c) => s & {{c, c/12*[SalesObjective], c/12*[ClientObjective]}}), {"Month", "SalesObjective", "ClientObjective"})), {"SalesObjective", "ClientObjective"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Month", "SalesObjective", "ClientObjective"}, {"Month", "SalesObjective", "ClientObjective"})
in
#"Expanded Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg1W0lEyAmEDIwMgZQhmmyjF6kQrhXoD2cYIOWMziAKwXDCqHFCLjpI5UC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountryId = _t, TypeId = _t, Year = _t, SalesObjective = _t, ClientObjective = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CountryId", type text}, {"TypeId", Int64.Type}, {"Year", Int64.Type}, {"SalesObjective", Int64.Type}, {"ClientObjective", Int64.Type}}),
#"Added Custom" = Table.RemoveColumns(Table.AddColumn(#"Changed Type", "Custom", each Table.FromRows(List.Accumulate({1..12}, {}, (s,c) => s & {{c, c/12*[SalesObjective], c/12*[ClientObjective]}}), {"Month", "SalesObjective", "ClientObjective"})), {"SalesObjective", "ClientObjective"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Month", "SalesObjective", "ClientObjective"}, {"Month", "SalesObjective", "ClientObjective"})
in
#"Expanded Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Amazing also! Thanks!
Hi, @Borja204
I assume you have a month-table that looks like below.
Please check the below sample pbix file's link and the formula for creating a new table.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan,
So impresive solution!! I'm accepting as answer the one that @MFelix posted just because is in power query and I think is cleaner and easier to understand. But your approach was awesome and I've learned a couple of things from your calculated table formula!
Big thanks!
Hi @Borja204 ,
What I did was to create a new table with the years / months:
Added a new column to this table with the name of the table with the sales objectives:
SalesObjectives
Expanded the new column
Then added two custom columns:
[Custom.SalesObjective]/12*[Month]
[Custom.ClientObjective]/12*[Month]
Deleted the previous two columns and final result below:
Check full code for the second table below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6pEQAwCATAXk4jgPy1MPTfRqIyh1y3EXB1hcCQ8uGMxuiMwZiMxdiMwzAtKgd7ibw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each SalesObjectives),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "TypeID", "Year", "SalesObjective", "ClientObjective"}, {"Custom.Country", "Custom.TypeID", "Custom.Year", "Custom.SalesObjective", "Custom.ClientObjective"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Salesobjective", each [Custom.SalesObjective]/12*[Month]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "ClientObjective", each [Custom.ClientObjective]/12*[Month]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom.SalesObjective", "Custom.ClientObjective"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.Country", Order.Ascending}, {"Year", Order.Ascending}, {"Month", Order.Ascending}})
in
#"Sorted Rows"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you Miguel,
Simple and clean!
Regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |