How to count once in a pie chart ?

[Fardy]

Hi,

I have 3 columns: Customer, date of birth and date of service.
From the last two columns, I create an Age column.

Then, I make a pie chart with a measure called NbCustomer, which is just a DistinctCount of the Customer column and I use Age for legend.

Finally, there's a segment for filtering by service date.

Currently in my powerBI : if we filter on a period (from 1/12 to 31/12) and the customer's birthday falls within the period (15/12 for example) then he is counted 2 times in the graph (but not in the total).

What I want : If the customer's birthday falls within the period, I count him once, based on the smallest age.

Thank you for your help 🙂 

[Daniel29195 · Accepted solution]

Hello @Fardy ,

check if this works for you. 

the one on the right is displaying 4 , however the one on the left is displaying 3  which is equal to the total. 

this is my test data : 

if this is the correct logic and this what you want to achieve, 
try this measure : 

Measure 5 =

var datasource  =

INDEX(1,

SUMMARIZE(

    'Table (6)',

    'Table (6)'[customerid],

    'Table (6)'[age column]

),

ORDERBY('Table (6)'[age column] , asc)

)

VAR res =

CALCULATE(

    DISTINCTCOUNT('Table (6)'[customerid]),

    KEEPFILTERS(datasource)

)

return res

let me know if this helps.  or if there something im missing . 

[amitchandak]

@Fardy , Create two measures like

Last Qty = Var _max = minx(filter( ALLSELECTED(Data1), Data1[Customer] = max(Data1[Customer]) ),Data1[Age])
return
CALCULATE(distinctcount(Data1[qty]), filter( (Data1), Data1[Customer] = max(Data1[Customer]) && Data1[Age] =_max))

Sum Last Qty = Sumx(VALUES(Data1[Customer]) , [Last Qty])