Forum Discussion
Solved
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 ...
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 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