Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have 2 tables that look like below. One has budget for the year by month and the other has sales by month. How can I show the difference for each month between what is forecasted and actuals if the data is in the format that it is below?
Acount # | Account Name | 2022-1 | 2022-2 |
Account1 | Account Name 1 | 3 units | 5 units |
Account2 | Account Name 2 | 2 units | 3 units |
Solved! Go to Solution.
Hi @Anonymous ,
In addition to amitchandak's reply, I think you need to do more transformation on your table. Your new table should look like as below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NKzFU0oExFfwSc1MVQALGCqV5mSXFQJYplBWrA9dhhK4DJGAE1wHTGxsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Acount #" = _t, #"Account Name" = _t, #"2022-1" = _t, #"2022-2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Acount #", type text}, {"Account Name", type text}, {"2022-1", type text}, {"2022-2", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Acount #", "Account Name"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Year-Month"}, {"Value", "Budget"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","units","",Replacer.ReplaceText,{"Budget"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Budget", Int64.Type}, {"Year-Month", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Year-Month", "YearMonth"}})
in
#"Renamed Columns1"
Then create a calculated column in "Sales" table.
Diff =
VAR _BUDGET = CALCULATE(SUM(Budget[Budget]),FILTER(Budget,Budget[Account Name] = EARLIER(Sales[Account Name])&&Budget[YearMonth] = EARLIER(Sales[YearMonth])))
VAR _SALES = Sales[Sales]
RETURN
_SALES - _BUDGET
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
In addition to amitchandak's reply, I think you need to do more transformation on your table. Your new table should look like as below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NKzFU0oExFfwSc1MVQALGCqV5mSXFQJYplBWrA9dhhK4DJGAE1wHTGxsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Acount #" = _t, #"Account Name" = _t, #"2022-1" = _t, #"2022-2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Acount #", type text}, {"Account Name", type text}, {"2022-1", type text}, {"2022-2", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Acount #", "Account Name"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Year-Month"}, {"Value", "Budget"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","units","",Replacer.ReplaceText,{"Budget"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Budget", Int64.Type}, {"Year-Month", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Year-Month", "YearMonth"}})
in
#"Renamed Columns1"
Then create a calculated column in "Sales" table.
Diff =
VAR _BUDGET = CALCULATE(SUM(Budget[Budget]),FILTER(Budget,Budget[Account Name] = EARLIER(Sales[Account Name])&&Budget[YearMonth] = EARLIER(Sales[YearMonth])))
VAR _SALES = Sales[Sales]
RETURN
_SALES - _BUDGET
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This works for the most part except all the accounts in the budget table are not accounted for in the sales table. Since only 1 month has happened I only have sales in let's say 230 of the total 300 budgeted accounts. So now I don't get a true representation of what the actual difference is since any account that doesn't have sales yet won't show the budgeted numbers either. I think I need to do a similar formula but use the budget table and create a relationship between the 2.
@Anonymous , Unpivot the table to have year on row
https://radacad.com/pivot-and-unpivot-with-power-bi
Then you can use
Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400
Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |