Calculating Turnover with a split

[andyor]

I have several rows in my dataset where the Turnover field needs to be split up into 3/4/5/6/7 parts, depending. 

The issue I face is that if say the turnover is $200 and I need to split it into 3 parts, this will assign $66.67 for each = $200.01, which isn't right. 

I know what I want to do out loud, but just don't know how to implement this in DAX. I'm thinking of calculating the sum of the parts thereafter, such that if the sum of the parts != to the original number, then subtract this from the first row with that ID. But there could be a more elegant way of doing this too!

Example rows below, before and (hopefully) after. 

Appreciate if anybody can help with this 🙂 

ID	RecordType	Turnover
2456	1	$200.00
2456	0	$0.00
2456	0	$0.00
		
ID	RecordType	Turnover
2456	1	$66.66
2456	0	$66.67
2456	0	$66.67

[Anonymous · Accepted solution]

Hi @andyor ,

Please try below steps:

1. below is my test table

Table:

2. create a measure and add it to table visual

Avg Tuno =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[ID] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id )
VAR ctn =
    COUNTROWS ( tmp )
VAR max_tuno =
    MAXX ( tmp, [Turnover] )
RETURN
    DIVIDE ( max_tuno, ctn )

Measure =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[ID] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id )
VAR max_tuno =
    MAXX ( tmp, [Turnover] )
VAR ctn =
    COUNTROWS ( tmp )
VAR _a =
    FORMAT ( [Avg Tuno], "0.00" )
VAR diff = ctn * _a - max_tuno
RETURN
    IF (
        diff > 0,
        IF ( SELECTEDVALUE ( 'Table'[RecordType] ) = 1, _a - diff, _a ),
        IF ( SELECTEDVALUE ( 'Table'[RecordType] ) = 1, _a + diff, _a )
    )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[andyor]

3 more examples below:

Nothing needs to be done with examples 1 and 3. But for example 2, since $350/6 rows with that same (row) ID calculates as $58.33 = $349.98. So we need to add on $0.02 to the row where RecordType = 1 to keep the same amount. 

Same as the original example, $200/3 rows with the same ID = $66.67. This totals $200.01. So we needed to subtract $0.01 from RecordType = 1 . 

ID	RecordType	Turnover
2457	1	$200.00
2457	0	$0.00
2457	0	$0.00
2457	0	$0.00

ID	RecordType	Turnover
2457	1	$50.00
2457	0	$50.00
2457	0	$50.00
2457	0	$50.00

ID	RecordType	Turnover
2458	1	$350.00
2458	0	$0.00
2458	0	$0.00
2458	0	$0.00
2458	0	$0.00
2458	0	$0.00

ID	RecordType	Turnover
2458	1	$58.35
2458	0	$58.33
2458	0	$58.33
2458	0	$58.33
2458	0	$58.33
2458	0	$58.33

ID	RecordType	Turnover
2459	1	$100.00
2459	0	$0.00

ID	RecordType	Turnover
2459	1	$50.00
2459	0	$50.00

[Anonymous]

Hi @andyor ,

I'm a little confused about your needs, Could you please explain them further? 

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu