Age distribution of Top Selling Customers

[rosh]

Hi there!

I am trying to obtain the top 10 customers by sales value and then return their respective dimensions (age, gender etc) into separate visuals. 

Currently I have the following measure which allows me to identify the top 10 customers using RANKX: 

TopN Customers sales =

CALCULATE([Customer Total Sales],

FILTER(VALUES(SalesDetail[CustomerID])

, COUNTROWS(

FILTER('TopN',

RANKX(ALL(SalesDetail[CustomerID]),[Customer Total Sales],,DESC,Dense) <= 'TopN'[Selected TopN]))

> 0 )

)

As mentioned, this will return the top customers sales. However now I want to also show the COUNT of the Age bands of these top 10 customers in a separate bar chart, that only indicates the age distribution and no sales data. 

 I've tried to do this using the same CALCULATE measure above, except returning the count of the age bins. This however doesnt work. 

Any guidance would be appreciated! 

[V-lianl-msft]

It's not clear if you can share a pbix file with virtual data?

[amitchandak]

@rosh , if you have age as column, then put that in histogram or your visual and use above measure as visual level filter for not blank

or try a measure like

CALCULATE([Age],TOPN('TopN'[Selected TopN]),allselected(SalesDetail[CustomerID]),[Customer Total Sales],DESC),VALUES(SalesDetail[CustomerID]))

[rosh]

Thanks for a quick response. 

Unfortunately neither options worked. The age is a column (bins), which is stored in the dimension table with a two way relationship onto the SalesDetail table. If I apply a visual filter, nothing is affected. 

Your above measure filters the age of topN per band it seems. So for TopN = 25, I am getting a count of age = 25 for each bin (if there is more than 25 counts of that age bin) in the histogram. 

This seems like it should be quite straight forward: 

Get top 25 customers ranked by SUM of their sales. 

Filter all visuals so that only the data relating to these top 25 customers is displayed. 

A visual filter would be ideal, as then I could apply it to any visual on the page. 

[rosh]

I cannot add a static TopN filter, as the topN needs to be dynamic based on a slicer.