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 measure/metric?

 

Thanks and Regards,

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.

15 Replies

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

    Does

    Contribution % =
    var _total =
    CALCULATE(
    SUM([Total Sales by Province]),
    ALL('tableName')
    )
    Return
    DIVIDE(
    SUM([Total Sales by Province]),
    _total,
    0
    )

     

    Get you the result you are looking for?

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