cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

## Need assistance with penetration percentage calculation

I have a report where I have suppliers (22 of them) and then companies who have done business with that supplier (95 possible customers)  I'm trying to determine the penetration by supplier based on what companies have done business with them.

While I know how to do this in Excel, I'm not sure how to do this in BI.  There is no numerical value for the the supplier or businesses.  They are text fields.  Obviosuly the sales are in currency.  How do I have a calculation that uses the supplier and the corresponding sales by business to show their level of penetration for my company as a whole?

Dataset is - Business, Supplier, Sales YTD 2023.

Any help is appreciated.

1 ACCEPTED SOLUTION
Community Support

To calculate the penetration rate of a supplier in Power BI Desktop, you can use the following DAX formula:

``````Penetration Rate =
DIVIDE(
CALCULATE(
DISTINCTCOUNT('Table'[Customer]),
FILTER(
'Table',
'Table'[Supplier] = MAX('Table'[Supplier])
)
),
DISTINCTCOUNT('Table'[Customer])
) * SUM('Table'[Sales YTD 2023])
``````

In this formula, replace ‘Table’ with the name of your table, ‘Customer’ with the name of your customer column, ‘Supplier’ with the name of your supplier column, and ‘Sales YTD 2023’ with the name of your sales column.

This formula calculates the penetration rate for a specific supplier by dividing the number of unique customers who have done business with that supplier by the total number of unique customers in your dataset. The result is then multiplied by the sum of sales for that supplier in YTD 2023.

You can use this formula to create a new measure in Power BI Desktop.

Best Regards,

Stephen Tao

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

Community Support

To calculate the penetration rate of a supplier in Power BI Desktop, you can use the following DAX formula:

``````Penetration Rate =
DIVIDE(
CALCULATE(
DISTINCTCOUNT('Table'[Customer]),
FILTER(
'Table',
'Table'[Supplier] = MAX('Table'[Supplier])
)
),
DISTINCTCOUNT('Table'[Customer])
) * SUM('Table'[Sales YTD 2023])
``````

In this formula, replace ‘Table’ with the name of your table, ‘Customer’ with the name of your customer column, ‘Supplier’ with the name of your supplier column, and ‘Sales YTD 2023’ with the name of your sales column.

This formula calculates the penetration rate for a specific supplier by dividing the number of unique customers who have done business with that supplier by the total number of unique customers in your dataset. The result is then multiplied by the sum of sales for that supplier in YTD 2023.

You can use this formula to create a new measure in Power BI Desktop.

Best Regards,

Stephen Tao

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors