Count distinct values in quartiles of a measure

[Moht]

Hello Community, 

Can someone  please help me with this head knot , 

In a table , i have sales by day and by costumer  and other dimensions

I have created a measure called

sales_last_day  = 

var maxday = max(table[date])

var sales_last = CALCULATE(sum(table[sales]),filter(table,table[date]=maxday))

return sales_last 

I would like to create a table visual that would look like this  , with the quartiles based on the sales_last_day measure

Quartiles	min value	max value	count of customers
Q1	0	(max value of Q1)	1000
Q2	(min value of Q2)	(max value of Q2)	340

... for the 4 quartiles 

Maybe with creating a table based on my measure , but i want it to be dynamic according to the filters selected in my page

thank you for your help 

Nouha 

[Ashish_Mathur]

Hi,

You are confusing Quartiles with Quarters.  Anyways, try this.  Create a Calendar Table with calculated column formulas for year, Month name, Month number and Quarters.  Sort the Month name column by the Month number column.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  To your visual, Year and Quarter from the Calendar Table.  Write these measures

Min = min(Data[Sales])

Max = max(Data[Sales])

Hope this helps.

[v-shex-msft · Accepted solution]

Hi @Moht,

You can try to use the following measure formula to get the lastdate sales based on current quarter group:

sales_last_day =
VAR maxday =
    CALCULATE (
        MAX ( table[date] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Quartiles] )
    )
RETURN
    CALCULATE (
        SUM ( table[sales] ),
        FILTER ( ALLSELECTED ( Table ), table[date] = maxday ),
        VALUES ( Table[Quartiles] )
    )

Regards,

Xiaoxin Sheng