powerbidaxaccounting

Allocating a Column’s value to a different Column in DAX

I have a report that I’m doing up and I’m having some issues with. Basically, if there exists a value in Cost Center 0 image image 1479×805 116 KB

I need to allocate that value to the other columns (e.g. 10, 11, 12, 13, 20 …): enter image description here

By these percentage rates (Table is Default Cost Center Allocation): image

And zero out that 0 Cost center column.

So using the row account 5040 under the Discounts category, you have a value of $3,371: enter image description here enter image description here

The math I need is for all cost centers but for example cost center 10 would be: $1,392 (original number for cost center 10) + ($3,371 * 10%)

Then I would follow the same convention of allocation to each cost center (e.g. 10, 11, 12, 13, 20 …) and finally the cost center 0 would display empty as it has been allocated to other cost centers.

Here is breakdown: enter image description here

In excel:

enter image description here

Here it is simplified; regardless if associated accounts (1) has data, I need each field to contain the associated lookup value like example (2) enter image description here

PBI: https://forum.enterprisedna.co/uploads/short-url/imfl4RENrOZbxpnaezeAYeII4r8.pbix

Solution

  • As you know already how to use mquery.

    Hereby the script to manipulate the data. I could not use your pbi file as the master table was not giving any data so I copied form PowerBI to excel and did a new import.

    The first 4 lines are the import so you do not need it.

    1. Filter to get only the cost centre 0 rows
    2. merge on the cost unit to get the % on how to divide the money.
    3. Expand only needed columns
    4. Add the columns Alloc where the calculation is happening
    5. Remove columns not needed from orig table and rename the other columns to match orig table
    6. Get all other rows from the orig table with cost center <> 0

    End result is extra rows in your orig table which you need to show in your report. The script you need to append to your existing GLTransactions table

    let
    Source = Excel.Workbook(File.Contents("C:\PowerBI\GLTransactions.xlsx"), null, true),
    GLTransactions_Sheet = Source{[Item="GLTransactions",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(GLTransactions_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"bkjrcode", Int64.Type}, {"ACCOUNT", Int64.Type}, {"dagbknr", Int64.Type}, {"COST CENTER", Int64.Type}, {"COST UNIT", Int64.Type}, {"docdate", type datetime}, {"PK Cost Center", Int64.Type}, {"PK Cost Unit", Int64.Type}, {"Segment Driver", type text}, {"Dollars with Allocation", Int64.Type}, {"Merged", Int64.Type}, {"Default Cost Center Allocation.Value", type number}, {"Cost Center Desc", type text}, {"bdr_hfl", Int64.Type}}),


    GetOnlyCostCenter0 = Table.SelectRows(#"Changed Type", each ([COST CENTER] = 0)),
    #"Filtered Rows1" = Table.SelectRows(GetOnlyCostCenter0, each [Dollars with Allocation] > 0),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows1", {"COST UNIT"}, #"Cost Unit Allocation", {"Cost unit"}, "Cost Unit Allocation", JoinKind.LeftOuter),
    #"Expanded Cost Unit Allocation" = Table.ExpandTableColumn(#"Merged Queries", "Cost Unit Allocation", {"Cost Center", "Description", "PK Cost Units Allocation", "Value"}, {"Cost Center.1", "Description", "PK Cost Units Allocation", "Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Cost Unit Allocation", "Alloc", each [Dollars with Allocation] * [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"COST CENTER", "Dollars with Allocation", "Cost Center Desc","PK Cost Center","PK Cost Unit","Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Cost Center.1", "Cost Center"}, {"Description", "Cost Center Desc"}, {"Alloc", "Dollars with Allocation"}, {"PK Cost Units Allocation", "PK Cost Unit"}}),
    GetExclCostCenter0 = Table.SelectRows(#"Changed Type", each ([COST CENTER] <> 0)),
    #"Appended Query" = Table.Combine({#"Renamed Columns", GetExclCostCenter0})
in
    #"Appended Query" // Here you have your new Transactions(gbkmut) table. It includes rows for each cost allocation