Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Budget vs sales monthly sales by account

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 Name2022-12022-2
Account1Account Name 13 units5 units
Account2Account Name 22 units3 units
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1675404528250.png

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.

RicoZhou_1-1675404983129.png

 

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.

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1675404528250.png

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.

RicoZhou_1-1675404983129.png

 

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.

Anonymous
Not applicable

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.

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.