Forum Discussion
Calculating Turnover with a split
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
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.
3 Replies
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 Yu3 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.00ID 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.33ID RecordType Turnover 2459 1 $100.00 2459 0 $0.00 ID RecordType Turnover 2459 1 $50.00 2459 0 $50.00Hi 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.
