Need Help Splitting Dealers "Other" Values by same dealer Retail SG Code in Power BI

hi dhanurjaya ,

not sure if i fully get you, you can write a calculated column like:

column = 
VAR _splitBy=
COUNTROWS(
    CALCULATETABLE(   
        VALUES(data[SG code]),
        ALLEXCEPT(data, data[Parent Customer], data[Month -Year]),
        data[SG Type]<>"Other"
    )
) 
VAR _othervalue =
CALCULATE(
    SUM(data[value]),
    ALLEXCEPT(data, data[Parent Customer], data[Month -Year]),
    data[SG Type]="Other"
)
VAR _splitvalue = DIVIDE(_othervalue, _splitBy)  
VAR _result = data[value] + _splitvalue
RETURN _result

it worked like:

If the filter context could be elaborated, a measure could be written with similar logic.

Hi dhanurjaya,  You can try the following steps to implement this logic in Power BI.

Step 1: Create a Calculated Column for the "Other" Values by Dealer.

• Go to Modeling > New Column and create a column to calculate the total "Other" value for each dealer.

  Total_Other_By_Dealer = 
  CALCULATE(
  SUM('Table'[Value]),
  'Table'[SG type] = "Other",
  ALLEXCEPT('Table', 'Table'[Parent Customer], 'Table'[Month -Year])
  )

• This will calculate the total "Other" value for each dealer and month.

Step 2: Count the Number of Retail SG Codes per Dealer

• Create another calculated column to count the number of "Retail" SG codes for each dealer.

  Retail_SG_Count = 
  CALCULATE(
  DISTINCTCOUNT('Table'[SG code]),
  'Table'[SG type] = "Retail",
  ALLEXCEPT('Table', 'Table'[Parent Customer], 'Table'[Month -Year])
  )

Step 3: Calculate the Split "Other" Value per Retail Row

• Create a new calculated column to calculate the split "Other" value for each Retail SG row.

  Split_Other_Value = 
  IF(
  'Table'[SG type] = "Retail",
  DIVIDE('Table'[Total_Other_By_Dealer], 'Table'[Retail_SG_Count], 0),
  BLANK()
  )

• This divides the total "Other" value by the number of "Retail" SG codes for each dealer.

Step 4: Calculate the Final Value (Retail + Split Other)

• Create another calculated column to calculate the final value for the Retail SG rows:

  Final_Value = 
  IF(
  'Table'[SG type] = "Retail",
  'Table'[Value] + 'Table'[Split_Other_Value],
  'Table'[Value]
  )

• Step 5: Create a Visual or Table to Display the Final Result

Please let me know if you need further adjustments or explanations!
If I have resolved your question, please consider marking my post as a solution. Thank you!

Hi dhanurjaya ,

not sure if i really get you, try to plot a measure like:

measure = 
VAR _splitBy=
COUNTROWS(
    CALCULATETABLE(   
        VALUES(data[SG code]),
        ALLEXCEPT(data, data[Parent Customer], data[Month -Year]),
        data[SG Type]<>"Other"
    )
)
VAR _othervalue =
CALCULATE(
    SUM(data[value]),
    ALLEXCEPT(data, data[Parent Customer], data[Month -Year]),
    data[SG Type]="Other"
)
VAR _splitvalue = DIVIDE(_othervalue, _splitBy)  
VAR _result = SUM(data[value]) + _splitvalue
RETURN IF(MAX(data[SG type]) <>"Other", _result)

it worked like:

hi,

it's fine but not final result, i deen below

when logic calculate at dealer SG level but i need when i see SG level total then its only sum final value not calculate logic (logic already calculate at dealer level in internal).

and one think if i select filter any by product/month/year etc.  anythink its should be calculate as per selection value. 

and when i count retail SG, remove filter from calender.

Hi dhanurjaya 

Sorry for the late reply, please try this:

Here I create a measure to calculate the Split:

Split =
VAR _currentCustomer =
    VALUES ( 'Table'[Parent Customer] )
VAR _countdate =
    DISTINCTCOUNT ( 'Table'[Month -Year] )
VAR _currentDate =
    MAX ( 'Table'[Month -Year] )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[SG type] ) = "Other",
        0,
        IF (
            _countdate = 1,
            SUMX (
                SUMMARIZE (
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[SG type] = "Other"
                            && 'Table'[Parent Customer]
                            IN _currentCustomer
                                && 'Table'[Month -Year] = _currentDate
                    ),
                    'Table'[Parent Customer],
                    "_AVG",
                        CALCULATE (
                            SUM ( 'Table'[Value] ),
                            FILTER (
                                ALLSELECTED ( 'Table' ),
                                'Table'[Parent Customer] = EARLIER ( 'Table'[Parent Customer] )
                            )
                        )
                ),
                [_AVG]
            ),
            IF (
                _countdate <> 1,
                AVERAGEX (
                    SUMMARIZE (
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            'Table'[SG type] = "Other"
                                && 'Table'[Parent Customer] IN _currentCustomer
                        ),
                        'Table'[SG code],
                        'Table'[Parent Customer],
                        "_AVG",
                            (
                                CALCULATE (
                                    SUM ( 'Table'[Value] ),
                                    FILTER (
                                        ALLSELECTED ( 'Table' ),
                                        'Table'[Parent Customer] = EARLIER ( 'Table'[Parent Customer] )
                                    )
                                )
                                    / COUNTROWS (
                                        SUMMARIZE (
                                            FILTER (
                                                ALLSELECTED ( 'Table' ),
                                                'Table'[SG type] = "Other"
                                                    && 'Table'[Parent Customer] IN _currentCustomer
                                            ),
                                            [Parent Customer],
                                            'Table'[Month -Year]
                                        )
                                    )
                            )
                    ),
                    [_AVG]
                )
            )
        )
    )

The result:

Then add a measure:

Sum of Final Value = SUM('Table'[Value])+[Split]

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.