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

get values instead of count

Hi,

 

    I have a measure that takes the customer ID table and takes the top 6% of customers with the highest sales and returns the total count of customers. Is there a way for me to get the values of who those customers are, instead of count? I tried applying the fname, lname columns next to the measure but it didn't show me anything (for obvious reasons). I not only want to know how many top customers we have, but who they are as well. How can I do that?

 

Here is my measure code:

FG_Cust_Count =
VAR customerPer = DISTINCTCOUNT('SALES DATA'[CUST_NUM])*0.06
return
CALCULATE([FG_Cust],FILTER(VALUES('SALES DATA'[CUST_NUM]),RANKX(VALUES('SALES DATA'[CUST_NUM]),[total sales],,desc) <= customerPer))
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: get values instead of count

@uahmed90,

 

To be general, you may modify your measure using DAX below:

FG_Cust_Count =
VAR customerPer =
    DISTINCTCOUNT ( 'SALES DATA'[CUST_NUM] ) * 0.06
RETURN
    CONCATENATEX (
        FILTER (
            VALUES ( 'SALES DATA'[CUST_NUM] ),
            RANKX ( VALUES ( 'SALES DATA'[CUST_NUM] ), [total sales],, desc ) <= customerPer
        ),
        'SALES DATA'[fname] & " "
            & 'SALES DATA'[lname],
        ","
    )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 REPLY 1
Community Support Team
Community Support Team

Re: get values instead of count

@uahmed90,

 

To be general, you may modify your measure using DAX below:

FG_Cust_Count =
VAR customerPer =
    DISTINCTCOUNT ( 'SALES DATA'[CUST_NUM] ) * 0.06
RETURN
    CONCATENATEX (
        FILTER (
            VALUES ( 'SALES DATA'[CUST_NUM] ),
            RANKX ( VALUES ( 'SALES DATA'[CUST_NUM] ), [total sales],, desc ) <= customerPer
        ),
        'SALES DATA'[fname] & " "
            & 'SALES DATA'[lname],
        ","
    )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.