Forum Discussion

Alex_192's avatar
Alex_192
Frequent Visitor
8 years ago

What IF parameter: using 2 as filter to extract some data

Hello everyone,

 

I am facing quite a complex issue (for me :) ) and I would really appreciate some guidance !

 

I have this kind of data:

 Client name / Sales amount / Date of sales

Client A1001/01/2017
Client A2001/02/2017
Client A3001/05/2017
Client A4001/08/2017
Client A5001/09/2017
Client A6001/02/2017
Client A1001/03/2017
Client B1001/01/2017
Client B2001/02/2017
Client B3001/05/2017
Client B4001/08/2017
Client B5001/09/2017
Client B6001/10/2017
Client B1001/12/2017
Client C1001/01/2017
Client C2001/02/2017
Client C3001/02/2017
Client C4001/02/2017
Client C5001/02/2017
Client C6001/02/2017
Client C1001/02/2017

 

I would like to look at the inactive clients based on 2 dynamic "what if" parameter:

- one would be a period of month (from 0 to 12), being the last X month from today

- one would be the minimum sales per month (from 0 to 100), being the minimum sales level from which I want to consider the client inactive

Those two what if should "say": select all clients which have bought not more than "10" sales in the last "3"months

 

My idea is then to show on the same report the list of those client + what was their sales in the 12 months before the inactive period selected (in my previous example before the last "3" months)

 

I understood that what if paramaters can only be used in measures, and I don't see how to manage this like that...

 

Any kind of help would be very much appreciated :)

 

Thanks 

 

Alex

Need Help

2 Replies

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

    Hi Alex_192,

     

    "What IF" parameter is not able to filter data. In your scenario, I would suggest you add a relative slicer from which users can choose a period of month.

     

    Create a "What IF" parameter which is used to specify the minimum sales per month.

     

    Create some measures similar to below:

    Sum per month =
CALCULATE (
    SUM ( Client_Sales[Sales Amount] ),
    ALLEXCEPT (
        Client_Sales,
        Client_Sales[Client Name],
        Client_Sales[Date of Sales].[Month]
    )
)

minmum sales = IF([Sum per month]>=Parameter[Parameter Value],1,0)

    Add above measure [minimum sales] into visual level filters, set its value to 0. In that case, once you specify a value for slicer and "What IF" parameter, the visual will display all clients which have bought not more than specified sales in the last X months.

     

    Best regards,

    Yuliana Gu

    • Alex_192's avatar
      Alex_192
      Frequent Visitor
      8 years ago

      Hi v-yulgu-msft,

       

      Thank you very much for your answer, I think it does exactly what I asked for !

      edit: after a few test, it doesnt' seem to work properly

       

       

      But I think I am missing something, let me explain:

       

      with this view I am able to see:

      - all client who bought less than "5k" over the last "3" months period 

       

      but I think I am not able to detect:

      - a client who used to buy before this 3 months period and who became inactive during the last 3 months

      - what amount of sales this client was doing on a 12 months period before those "3 months"

       

       

      Do you think that's feasible ?

       

      I found an other solution which basically consist in creating a client classification based on their sales done on previous quarters and then allocate e a status as "Inactive since 1Q" "Inactive since 2Q" ....

      But with this solution I can't play with the period (it has to be Q-1; Q-2; Q-3;...) and it's really white or black, either the client bought something either it's 0... I can't play with a variable like "who bought less than xx in the last 3 months" to tag this client as an "inactive".

       

      I don't know if I am clear enough, anyway thank you in advance for your time I really appreciate

       

      Kind regards,

       

      Alexandre