cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thales123
Frequent Visitor

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 than "40.000" in a year.

 

I have a measure named "Net Sales" that sums all the registers and I can list with the customers and its total. I'm trying to filter Net Sales only values greater than 40.000 but it does not work for all the visuals or for all the page, only in certain visuals. (ex. I have a field that counts the number of customers per year, if I filter that way, it keeps showing the same number, that is the total).

 

Another way to do it would be if I could create a column saying that the customer is "Key Customer", the problem is that I do not know how to do it, since I need to aggregate the sum based on the customer name and each year.

1 ACCEPTED SOLUTION

Hi Guys,

 

Actually it is possible!!!

 

The solution can be seen as this video example:

https://www.youtube.com/watch?v=djLX6IUWVwY&t=374s

 

Thank you all!!!

View solution in original post

8 REPLIES 8
Phil_Seamark
Microsoft
Microsoft

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.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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.

Dear @Jaap_Olsthoorn 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.Same filters, different values... I`d like them to be linked.

Hi @thales123,

 

See if my solution here helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Guys,

 

Actually it is possible!!!

 

The solution can be seen as this video example:

https://www.youtube.com/watch?v=djLX6IUWVwY&t=374s

 

Thank you all!!!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors