Skip to main content
cancel
Showing results for 
Search instead 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

Reply
andyor
Resolver I
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 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1663318505003.png

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 )
    )

vbinbinyumsft_1-1663318659916.png

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @andyor ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1663318505003.png

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 )
    )

vbinbinyumsft_1-1663318659916.png

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
Resolver I
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 ,

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

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.