Forum Discussion

vladimirkureza's avatar
vladimirkureza
Frequent Visitor
8 years ago

Subset Funnel

Hi,

I am trying to create a funnel where each layer is a subset of the previous.

I have next data model:



formula AMTchecks = IF(COUNTx(filter(checks; checks[customers_id] = customers[id]); checks[id]) >=1;COUNTx(filter(checks; checks[customers_id] = customers[id]); checks[id]);0)

if i build a sales funnel using attributes "AMTchecks" and "Count of id" as below:

 

then everything (filters and slicers) works fine, but each layer is not a subset of the previous.

Question: How to create the same sales funnel but where each layer is a subset of the previous?

I tried to create the following calculated table:

sales_funnel = DISTINCT(customers[AMTchecks])
amount_client = CALCULATe(COUNT(customers[id]); FILTER(customers; customers[AMTchecks] >= sales_funnel[AMTchecks]))

 

And build a sales funnel using these attributes as below:

 

the data is correct (each layer is a subset of the previous), but filters and slicers don't work.

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    vladimirkureza

     

    In this scenario, you have your amount client answer the filter and slicer, you should create a measure instead of a calculated column. You can write your measure like below:

     

    amount_client =
CALCULATE (
    COUNTA ( Customer[ID] ),
    FILTER ( ALL ( Customer ), Customer[AMTChecks] >= MAX ( Customer[AMTChecks] ) )
)

     

    Regards,

    • vladimirkureza's avatar
      vladimirkureza
      Frequent Visitor
      8 years ago

      Thanks for the answer!
      But the question could not be solved in this way. As shown below, in this case each layer is not a subset of the previous (on the right, a funnel created using a calculated table, where each layer is a subset of the previous):