percentage de penetration

[keylorbb]

Hello, I have a database of more than 5 million lines with billing information, each line with the customer code that you bought, that is, you can have 1 line for one customer while another customer can have 10 lines. I want to calculate the percentage of penetration of a product in a certain period, the percentage of penetration is calculated: how many customers bought me at least 1 product "x" among the total number of customers who bought in that period, that is, in January, they sold me 100 clients but only 10 customers bought at least one fragrance, my fragrance penetration is 10%. thank you very much

[v-yulgu-msft]

Hi @keylorbb,

Please post thread in English so that community members can better understand your requirement. Besides, please post sample data and show us your desired output so that I can try to reproduce the same scenario on my side.

How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[keylorbb]

Hello, thank you very much for you answer. I made the correction requested, can you help me?

[v-yulgu-msft · Accepted solution]

Hi @keylorbb,

Based on below sample data, please create a calculated column.

percentage of penetration per product =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Test Data'[Customer] ),
        ALLEXCEPT (
            'Test Data',
            'Test Data'[Date].[Year],
            'Test Data'[Date].[MonthNo],
            'Test Data'[Product]
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Test Data'[Customer] ),
        ALLEXCEPT ( 'Test Data', 'Test Data'[Date].[Year], 'Test Data'[Date].[MonthNo] )
    )
)

Best regards,

Yuliana Gu