topic Re: Calculating Turnover with a split in DAX Commands and Tips

Calculating Turnover with a split

andyor — Thu, 15 Sep 2022 05:23:13 GMT

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

Re: Calculating Turnover with a split

Anonymous — Fri, 16 Sep 2022 06:53:19 GMT

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

Re: Calculating Turnover with a split

andyor — Fri, 16 Sep 2022 07:45:33 GMT

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

Re: Calculating Turnover with a split

Anonymous — Fri, 16 Sep 2022 08:59:01 GMT

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.