Need assistance with penetration percentage calculation

[datadelta]

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.

[Anonymous · Accepted solution]

Hi @datadelta ,

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.