topic Re: Distribute cost and add rows? in DAX Commands and Tips

Distribute cost and add rows?

ays1977 — Tue, 14 Feb 2023 17:50:01 GMT

Hi,

New to PowerBI and DAX and hope you can help me with the solution. I have cost my income statment the I want to allocate/distribut to a internal number. For some cost I already have the cost on the correct internal number but for som OH cost I need to distribute the cost. For each month the share of each internal number will varies and I also have 2 different result units.

The figures from the income statment as below:

Date	Ver.Nr.	Account	Result unit	Internal number	Amount
20230102	34113252	Rent	1		15000
20230102	34113252	Income	1	12345	300
20230103	34126272	Insurance	1		25000
20230103	34126272	IT	1		3000
20230103	34126272	Income	2	23456	500
20230103	34126272	Insurance	2		20000
20230103	34126272	Rent	2		10000
20230201	34126255	Rent	1		50000

I also have a table with the share for each internal number per month and resultunit.

Period	Result unit	Internal number	Share
jan-23	1	12345	25%
jan-23	1	456789	25%
jan-23	1	321654	50%
jan-23	2	23456	40%
jan-23	2	987654	35%
jan-23	2	987999	25%
feb-23	1	12345	35%
feb-23	1	456789	12%
feb-23	1	321654	53%

What I need, if it´s possible, is for each row that don´t have an internal number I need to slit the amout according the the share for that month and add rows with the date, account, result unit and internal number so I end up with a table as below. Is this possible to do?

Date	Ver.Nr.	Account	Result unit	Internal number	Amount
20230102	34113252	Rent	1	12345	3750	0,25 of 15000
20230102	34113252	Rent	1	456789	3750	0,25 of 15000
20230102	34113252	Rent	1	321654	7500	0,5 of 15000
20230102	34113252	Income	1	12345	300
20230103	34126272	Insurance	1	12345	6250	0,25 of 25000
20230103	34126272	Insurance	1	456789	6250	0,25 of 25000
20230103	34126272	Insurance	1	321654	12500	0,5 of 25000
20230103	34126272	IT	1	12345	750	0,25 of 3000
20230103	34126272	IT	1	456789	750	0,25 of 3000
20230103	34126272	IT	1	321654	1500	0,5 of 3000
20230103	34126272	Income	2	23456	500
20230103	34126272	Insurance	2	23456	8000	0,4 of 20000
20230103	34126272	Insurance	2	987654	7000	0,35 of 20000
20230103	34126272	Insurance	2	987999	5000	0,25 of 20000
20230103	34126272	Rent	2	23456	4000	0,4 of 10000
20230103	34126272	Rent	2	987654	3500	0,35 of 10000
20230103	34126272	Rent	2	987999	2500	0,25 of 10000
20230201	34126255	Rent	1	12345	17500	0,35 of 50000
20230201	34126255	Rent	1	456789	6000	0,12 of 50000
20230201	34126255	Rent	1	321654	26500	0,53 of 50000

Regards

Re: Distribute cost and add rows?

wdx223_Daniel — Wed, 15 Feb 2023 03:53:11 GMT

NewTable= FILTER ( SELECTCOLUMNS ( GENERATE ( IncomeStatement, SELECTCOLUMNS ( FILTER ( ShareTable, ShareTable[Period] = FORMAT ( IncomeStatement[Date], "mmm-yy" ) && ShareTable[Result unit] = IncomeStatement[Result unit] ), "Res_U", ShareTable[Result unit], "Inter_Num", ShareTable[Internal number], "share", ShareTable[Share] ) ), "Date", IncomeStatement[Date], "Ver.Nr.", IncomeStatement[Ver.Nr.], "Account", IncomeStatement[Account], "Result Unit", IncomeStatement[Result unit], "Internal Number", COALESCE ( IncomeStatement[Internal number], [Inter_Num] ), "Amount", IF ( ISBLANK ( IncomeStatement[Internal number] ), IncomeStatement[Amount] * [share], IF ( IncomeStatement[Internal number] = [Inter_Num], IncomeStatement[Amount] ) ) ), [Amount] )

Re: Distribute cost and add rows?

ays1977 — Wed, 15 Feb 2023 19:46:47 GMT

Thank you wdx223_Daniel but when I try i get an error message that says that "DAX Comparisons cannot compare values of type Date with values of type TEXT". Can you please share excelfiles and pbix-file or do you have the solution how I can change this? Regards

Re: Distribute cost and add rows?

wdx223_Daniel — Fri, 17 Feb 2023 02:39:06 GMT

you must keep the data types be consistent. Date value can not compare with Text value.

here is the file for your reference.