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.

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

Hi,

Please show the expected result very clearly.

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

Hi Ashish_Mathur,

 

Please kindly help to check the screenshot below that doing the total sales by client vs. total sales by province & client.

 

 

Thanks and Regards,

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ADSL ,

     

    Here I suggest you to try this measure.

    Measure = 
VAR _Total_By_Client = CALCULATE(SUM('Table'[Total Actial Sales]),ALLEXCEPT('Table','Table'[Client Name]))
RETURN
DIVIDE(SUM('Table'[Total Actial Sales]),_Total_By_Client)

    My Sample:

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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