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 ...
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.

    • thales123's avatar
      thales123
      Frequent Visitor
      8 years ago

      Not really, what we would like to have in the report is a filter where I could see only the results from the Key Customer. The main issue is that We would like to have a lot of data to analyse through this filter, what is impossible if we use summarize, and since I can not filter a sum of a measure (like a minimum value of total) I'm not able to do it.

       

      Let's say I want to know, for Key Customers only how much they have bought from a specific category of product, or a specific item.

       

      I'm almost doing this outside of Power BI via Excel, I will need some rework, but it will work. In Power BI there are some limitations for this specific need I have.

       

      Thanks anyway for your support.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi,

         

        I don't think that will be possible.  The only way to create a slicr for key customer is to create a seperate table or in the data table itself, have a sperate column identifying the Type of Customer.  Even if you can create that spare column in your data table, the problem would be that a slicer selection will not recopute that spare column.  Calculated column formulas only update when the model is refreshed (not when a slicer selection is made).