topic Re: How to calculate penetration of the company in DAX Commands and Tips

How to calculate penetration of the company

Layziane — Thu, 19 Jan 2023 20:24:41 GMT

Hello,

I need help for make a DAX about penetration.
Our database have several companies and the date are different that these companies start the operations.
For example, the "Company A" starts in october and the "Company B" starts in december. So, I need to calculate the penetranion from when the company enters.
Another example, in april have 10 companies with the total of 100 customers each (total of 1000 customers) and we made 250 contracts, the penetration is 25%.
In may enters a new company and we have 11 companies with 100 customers each (total of 1000 customers) and we made 250 contracts, the penetration is 22%.
But if we want to calculate the penetration of april in november, we can't considerate the companies that starts after april.
In resume, we need just calculate the penetration when the company starts starts his operation.

Re: How to calculate penetration of the company

Anonymous — Fri, 20 Jan 2023 04:53:40 GMT

Hi Layziane

Can you please provide sample Data.

Re: How to calculate penetration of the company

Anonymous — Fri, 20 Jan 2023 06:31:40 GMT

Hi Layziane ,

Due to I don't know your data model, here I create a sample to have a test.

Company:

Contracts:

DimDate:

DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Relationship:

Measure:

Measure = VAR _COMPANY = CALCULATE ( COUNT ( Company[Company] ), FILTER ( ALL ( Company ), Company[Start Date] <= MIN ( DimDate[Date] ) ) ) VAR _CUSTOMER = 100 VAR _CONTRACTS = CALCULATE ( SUM ( Contracts[Contracts] ), FILTER ( Contracts, Contracts[YearMonth] = SELECTEDVALUE ( DimDate[YearMonth] ) ) ) RETURN DIVIDE ( _CONTRACTS, _COMPANY * _CUSTOMER )

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.

Re: How to calculate penetration of the company

Layziane — Fri, 20 Jan 2023 12:05:22 GMT

The "dataSolicitacaoEmprestino" is the date with the company starts, just start with the firts contract