Calculate the Contribution % by Province

[ADSL]

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,

[jgeddes · Accepted solution]

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 · Accepted solution]

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

)

[jgeddes]

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]

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]

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]

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]

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]

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 · Accepted solution]

How about

Contribution2 % =

var _total =

CALCULATE(SUM('Order'[Amt No VAT]),ALLEXCEPT('Order','Order'[Province]))

Return

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

0

)

[ADSL]

Hi @jgeddes ,

For example -- If we want to know the contribution of client name "Sprite" by province.

Any suggestion?

Thanks and Regards,

[jgeddes · Accepted solution]

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

)

[ADSL]

Hi @jgeddes ,

Thank you very much for your helpful measure.

Best Regards,

[Ashish_Mathur]

Hi,

Please show the expected result very clearly.

[ADSL]

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 · Accepted solution]

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]

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.

[ADSL]

Hi @Anonymous,

I am trying your measure suggestion, but it seem not working. Please kindly assist to check the screenshot below.

You can go through the sample file pbix - https://drive.google.com/drive/folders/1e8V0wERSSO7ildCMiM8nOYei2b_91n9D?usp=sharing