Forum Discussion

thales123's avatar
thales123
Frequent Visitor
8 years ago
Solved

Filter Measure with its Aggregated Value by Year

Hi Guys,   I've searched around and haven't found a solution for my need.   I have a sales database and I need to declare "Key Customer" to certain special customers in which have purchased more ...
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

HI thales123

 

Can it change from year to year and are you basing it on a Calendar year?

 

Would you be happy with a calculated column in your table that marks each sales record as belonging to a key customer or not?

 

Can you please post a small sample of your sales table.

 

 

thales123's avatar
thales123
Frequent Visitor
8 years ago

Dear Phil_Seamark thanks for the quick response,

 

I have a calendar DB to use the filters properly, I want to use the filter per year, but each year I have different sales values for each customer, so maybe a customer was Key-Customer in 2015 but not in 2016.

 

I do not know if a calculated column would solve it, I'm using an explicit measure already to calculate the sales values, but I need to declare at the records inside an year if it should be considered key or not, so that the filter would work for all my measures.

 

I'm going to share a sample DB (I'm keeping all the columns, but in yellow are the ones that matter) to help you up. There are some notes also, some filters we need to consider.

 

Sample excel: https://goo.gl/9KuCKZ

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hmm you have this cool sales table that I think you can derive a nice customer table from. You should start by extracting the year from the date in the sales table.

     

    How about this?

     

    Create a new table:

     

    CustomersYear = SUMMARIZE('bd_sales', Cliente, Year, "Sales", SUM(sales)) 

    Something like that will give you a list of customers, years and the net sales for those years. From this you can easily create your calculated column in the CustomersYear table

    KeyCustomer = IF(CustomersYear[Sales]>40000, 1,0). You can then Connect these two tables to each other in the data in the data model, which would allow you to segment your sales data by the KeyCustomer value.

     

    This is not the most elegant solution, but it will probably work.

    • thales123's avatar
      thales123
      Frequent Visitor
      8 years ago

      Dear Anonymous the way you have written actually helps me to define if a customer is key or not, that is way perfect, thank you very much.

       

      But still one problem, after the summarize it seems that I lose the link between the filters, so let's say that all of my other measures do not work linked with this analysis (I even tried to make relationships but it seems not to be able between them tables).

       

      So there are two options and to make it more professional I'd like to know if there is a way to make it happen.

      • Can I have an alternative to use this filter (key customer) linked with my original measures?
      • Or will I have to make a summarize a little bit heavier (with more data), create new measures and new filter?

      I am attaching a picture for you guys to check it out easily.

       

      Thanks for all the support!! Really thanks!Same filters, different values... I`d like them to be linked.