Forum Discussion

Post 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
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
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
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.

jgeddes

Super 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,

)

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])

)

Return

IF(

_inScope,

_clientCalc,

_provinceCalc

)

ADSL

Post 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
Super 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.
- ADSL
  Post Prodigy
  3 years ago
  Hi jgeddes ,
  
  I am trying your measure suggestion, but it's not working as expectation. Please kindly assist to check the screenshot below.
  
  I think it should be "Total Sales of client and each province" divide by "grand total"
  
  You can check the sample data of my pbix file - https://drive.google.com/file/d/1maX8nwvhc2TIUUVML59cvCUnsPG20PeI/view?usp=sharing
  - jgeddes
    Super User
    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
    )