Creating a dynamic summarization table based on measure values

[HeevaCh]

Hello everybody!

I have categorized my clients into 4 LRFM segments: Key, Frequent, Spender & Uncertain. Using measure.

Client	Status
A	Key
B	Uncertain
C	Uncertain
D	Frequent
E	Key
F	Key
G	Frequent
H	Key
I	Spender
J	Spender
K	Frequent
L	Uncertain

 I want a summarization table that shows something like this.

Status	# Clients
Key	4
Uncertain	3
Frequent	3
Spender	2

Notice that my output in the first table is measure. But we can’t use measure as a legend. I don’t want to use columns because I want it to be dynamic and the summarization values change based on the slicers I have.

What should I do?

My lrfm measure:

LRFM Analysis LRFM = 

IF([If Normalize L LRFM]="High" && [If Normalize F LRFM]="High" && [If Normalize M LRFM]="High" && [If Normalize R LRFM]="High","Key",

IF([If Normalize L LRFM]="Low" && [If Normalize F LRFM]="High" && [If Normalize M LRFM]="High" && [If Normalize R LRFM]="High","Key",

IF([If Normalize L LRFM]="High" && [If Normalize F LRFM]="High" && [If Normalize M LRFM]="High" && [If Normalize R LRFM]="Low","Key",

IF([If Normalize L LRFM]="Low" && [If Normalize F LRFM]="High" && [If Normalize M LRFM]="High" && [If Normalize R LRFM]="Low","Key",

IF([If Normalize L LRFM]="High" && [If Normalize F LRFM]="Low" && [If Normalize M LRFM]="High" && [If Normalize R LRFM]="High","Spender",

IF([If Normalize L LRFM]="Low" && [If Normalize F LRFM]="Low" && [If Normalize M LRFM]="High" && [If Normalize R LRFM]="High","Spender",

IF([If Normalize L LRFM]="High" && [If Normalize F LRFM]="Low" && [If Normalize M LRFM]="High" && [If Normalize R LRFM]="Low","Spender",

IF([If Normalize L LRFM]="Low" && [If Normalize F LRFM]="Low" && [If Normalize M LRFM]="High" && [If Normalize R LRFM]="Low","Spender",

IF([If Normalize L LRFM]="High" && [If Normalize F LRFM]="High" && [If Normalize M LRFM]="Low" && [If Normalize R LRFM]="High","Frequent",

IF([If Normalize L LRFM]="Low" && [If Normalize F LRFM]="High" && [If Normalize M LRFM]="Low" && [If Normalize R LRFM]="High","Frequent",

IF([If Normalize L LRFM]="High" && [If Normalize F LRFM]="High" && [If Normalize M LRFM]="Low" && [If Normalize R LRFM]="Low","Frequent",

IF([If Normalize L LRFM]="Low" && [If Normalize F LRFM]="High" && [If Normalize M LRFM]="Low" && [If Normalize R LRFM]="Low","Frequent",

IF([If Normalize L LRFM]="High" && [If Normalize F LRFM]="Low" && [If Normalize M LRFM]="Low" && [If Normalize R LRFM]="High","Uncertain",

IF([If Normalize L LRFM]="Low" && [If Normalize F LRFM]="Low" && [If Normalize M LRFM]="Low" && [If Normalize R LRFM]="High","Uncertain",

IF([If Normalize L LRFM]="High" && [If Normalize F LRFM]="Low" && [If Normalize M LRFM]="Low" && [If Normalize R LRFM]="Low","Uncertain",

IF([If Normalize L LRFM]="Low" && [If Normalize F LRFM]="Low" && [If Normalize M LRFM]="Low" && [If Normalize R LRFM]="Low","Uncertain","Not Found"))))))))))))))))

[HeevaCh]

I edited the code a little bit, In case any user using this code:

# Clients = 

VAR TempTable =

    ADDCOLUMNS ( ALL ( 'VBI_JobProgressReport'[Client closed] ), "Status", [LRFM Analysis LRFM] )

RETURN

    sumx( 

        VALUES ( 'CustomerCategory'[Category] ),

        CALCULATE(DISTINCTCOUNT(VBI_JobProgressReport[Client Closed]),FILTER ( TempTable, [Status] = 'CustomerCategory'[Category] ) )

    )

I used distinct count becuase I wanted to count my clients and in my dataset I had multiple rows with the same client.

[xifeng_L · Accepted solution]

Hi @HeevaCh ,

You can try this solution.

Step 1: Use the following table expression to create an auxiliary table of customer categories to be used as row label fields.

CustomerCategory = 
DATATABLE (
    "Category",STRING,
    "Index",INTEGER,
    {
        {"Key",1},
        {"Uncertain",2},
        {"Frequent",3},
        {"Spender",4}
    }
)

Step 2: Create a measure named # Clients.

# Clients =
VAR TempTable =
    ADDCOLUMNS ( ALL ( 'Client'[Client] ), "Status", [LRFM Analysis LRFM] )
RETURN
    SUMX (
        VALUES ( 'CustomerCategory'[Category] ),
        COUNTROWS ( FILTER ( TempTable, [Status] = 'CustomerCategory'[Category] ) )
    )

Step 3: Use the category field of the CustomerCategory table created earlier as the matrix row label and the # Clients measure as the value field of the matrix. Then you can get the results you want.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[HeevaCh]

Thanks!