Forum Discussion

ADSL's avatar
ADSL
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Calculate the Contribution % by Province

Hi BI Community Team,   I want to calculate the contribution % by province of total sales as the table below.   ==> Formula = Grand Total Sales / Total Sales by Province   Any suggestion of...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    How about

    Contribution2 % =
    var _total =
    CALCULATE(SUM('Order'[Amt No VAT]),ALLEXCEPT('Order','Order'[Province]))
    Return

    DIVIDE(SUM('Order'[Amt No VAT]), _total,
    0
    )

     

  • jgeddes's avatar
    jgeddes
    3 years ago

    Are you looking for this

    Contribution3 % =
    Var _Total_By_Client =
    CALCULATE(
        SUM('Order'[Amt No VAT]),
        ALL('Order'[Province])
    )
    Var _clientTotal =
    SUM('Order'[Amt No VAT])
    Return
    DIVIDE(
        _clientTotal,
        _Total_By_Client,
        0
    )
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    To your Table visual, drag Province and Client Name.  Write these measures

    Sales = sum(Data[Total Actual Sales])

    Total sales = calculate([Sales],all(Data[Province]))

    Contribution (%) = divide([Sales],[Total sales])

    Hope this helps.

ADSL's avatar
ADSL
Icon for Post Prodigy rankPost Prodigy
3 years ago

Hi jgeddes,

 

Sorry...I had missing the request information. Actually we have different client but direct service the same province so then we want to know the contribution % by province and eash client.

 

 

Any suggestion of measure to calculate it?

 

Best Regards,

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
3 years ago

I am not completely understanding your desired outcome but here are a couple of measures that might work for you.

Contribution % =
var _total =
CALCULATE(
SUM([Total Actual Sales]),
ALL('table (9)')
)
Return
DIVIDE(
SUM([Total Actual Sales]),
_total,
0
)
 
inScope Contribution % =
var _inScope =
ISINSCOPE('Table (9)'[Client Name])
var _provinceCalc =
[Contribution %]
var _clientCalc =
DIVIDE(
    SUM('Table (9)'[Total Actual Sales]),
    CALCULATE(
        SUM('Table (9)'[Total Actual Sales]),
        ALLEXCEPT('Table (9)','Table (9)'[Province])
    ),
    0
)
Return
IF(
    _inScope,
    _clientCalc,
    _provinceCalc
)
  • ADSL's avatar
    ADSL
    Icon for Post Prodigy rankPost Prodigy
    3 years ago

    Hi jgeddes,

     

    Yes, understand because one sales rep serivce for multiple clients then want to know the contribution by province and client as well.

     

    Please kindly help to check and assist again as the screenshot below.

     

    Any suggestion?

    • jgeddes's avatar
      jgeddes
      Icon for Super User rankSuper User
      3 years ago

       

      Contribution2 % =
      var _total =
      CALCULATE(
      SUM([Total Actual Sales]),
      ALLEXCEPT('table (9)', 'Table (9)'[Client Name])
      )
      Return
      DIVIDE(
      SUM([Total Actual Sales]),
      _total,
      0
      )
       
      This should work for you.