Forum Discussion

ADSL's avatar
ADSL
Icon for Post Prodigy rankPost Prodigy
4 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
    4 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
    4 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
    4 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
4 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
4 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.