topic Re: Add custom Row based on Dax logic in DAX Commands and Tips

Add custom Row based on Dax logic

Puja — Fri, 22 Nov 2024 21:05:35 GMT

Hello All,

I need some help with Dax. The data is grouped by ID. In Type column, the highlighed rows are need to build based om the logic.

Ex: Asset Purchases , add a row by adding above 2 rows ((Wages-Temp Staff +Payroll Tax Temp) *-1 ) then create a new row which is "Asset Purchases"

FYI, Asset Purchases is not from dataset.

Thanks in advance

Re: Add custom Row based on Dax logic

DataNinja777 — Sat, 23 Nov 2024 04:32:08 GMT

Hi Puja ,

Are you trying to create bookkeeping journal entries with debits and credits that balance to zero? If so, I recommend using Power Query to generate double-entry bookkeeping records. DAX is not ideal for this task, but Power Query excels at creating structured double-entry bookkeeping entries. I use it for this purpose regularly.

Here’s how you can do it:

Process in Power Query:

Start with your transaction data table.
Create a custom column named "Asset Purchases" that calculates the value based on your formula:

("Wages - Temp Staff" + "Payroll Tax Temp") * -1

Unpivot the columns so that you have the debits and credits structured in rows.

Below is the Power Query M code for this process:

let // Load your transaction data Source = Table.FromRows( { {"ID001", "Wages - Temp Staff", 1000}, {"ID001", "Payroll Tax Temp", 200}, {"ID002", "Wages - Temp Staff", 1500}, {"ID002", "Payroll Tax Temp", 300} }, {"ID", "Type", "Value"} ), // Group the data by ID GroupedData = Table.Group( Source, {"ID"}, { {"AllData", each _, type table [ID=nullable text, Type=nullable text, Value=nullable number]} } ), // Add a custom row for "Asset Purchases" based on the logic AddAssetPurchases = Table.TransformColumns( GroupedData, {"AllData", each Table.Combine({ _, Table.FromRows( {{[ID = List.First(_[ID]), Type = "Asset Purchases", Value = ([Value]{0} + [Value]{1}) * -1]}}, Table.Type(_) ) })} ), // Expand the grouped data back to a flat table ExpandedData = Table.ExpandTableColumn(AddAssetPurchases, "AllData", {"ID", "Type", "Value"}), // Unpivot if required (for debit/credit separation) UnpivotedData = Table.UnpivotOtherColumns(ExpandedData, {"ID", "Type"}, "Attribute", "Value") in UnpivotedData

This method ensures your bookkeeping entries balance to zero by automatically generating the corresponding "Asset Purchases" entry and formatting the data appropriately. Let me know if you need further assistance!

Best regards,

Re: Add custom Row based on Dax logic

Poojara_D12 — Mon, 25 Nov 2024 04:46:54 GMT

Hi Puja

To accomplish this in DAX, you need to create a calculated table or use measures to dynamically calculate the new row based on the provided logic.

Solution Using a Calculated Table

EnhancedTable = UNION( 'OriginalTable', -- Keep all original rows ADDCOLUMNS( SUMMARIZE( FILTER('OriginalTable', 'OriginalTable'[Type] IN {"Wages - Temp Staff", "Payroll Tax Temp"}), 'OriginalTable'[ID] ), "Type", "Asset Purchases", "Value", (CALCULATE(SUM('OriginalTable'[Value]), 'OriginalTable'[Type] = "Wages - Temp Staff") + CALCULATE(SUM('OriginalTable'[Value]), 'OriginalTable'[Type] = "Payroll Tax Temp")) * -1 ) )

Solution Using a Measure (If no new rows are required)

If you want to dynamically calculate the value for "Asset Purchases" in visuals (like a table), use a measure:

Asset Purchases = VAR TempStaffValue = CALCULATE(SUM('OriginalTable'[Value]), 'OriginalTable'[Type] = "Wages - Temp Staff") VAR PayrollTaxValue = CALCULATE(SUM('OriginalTable'[Value]), 'OriginalTable'[Type] = "Payroll Tax Temp") RETURN (TempStaffValue + PayrollTaxValue) * -1

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Add custom Row based on Dax logic

Anonymous — Fri, 29 Nov 2024 05:53:18 GMT

Thanks for the reply from Poojara_D12 and DataNinja777, please allow me to provide another insight:

Hi, Puja
Could you please let me know if the responses from Poojara_D12 and DataNinja777 have resolved your issue?

If it did, kindly accept it as the solution.

Below is my M language solution to your problem:

let // Load your transaction data Source = Table.FromRows( { {"ID001", "Wages - Temp Staff", 1000}, {"ID001", "Payroll Tax Temp", 200}, {"ID002", "Wages - Temp Staff", 1500}, {"ID002", "Payroll Tax Temp", 300} }, {"ID", "Type", "Value"} ), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Value", each List.Sum([Value])*-1, type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Type", each "Asset Purchases"), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Type", type text}}), #"Appended Query" = Table.Combine({#"Changed Type1", #"Changed Type"}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"ID", Order.Ascending}}) in #"Sorted Rows"

Here is my sample data:

The final result is as follows:

Of course, Poojara_D12 's UNION() function is also a good solution. To facilitate your understanding, I have also created the following example.

Here's my final result, which I hope meets your requirements.

For further details, please refer to:

UNION function (DAX) - DAX | Microsoft Learn

SUMMARIZE function (DAX) - DAX | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.