cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## 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``````

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @andyor ,

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 3
Anonymous
Not applicable

Hi @andyor ,

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.

Resolver I

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
Not applicable

Hi @andyor ,

Best regards,
Community Support Team_ Binbin Yu

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors