topic Re: Creating a dynamic summarization table based on measure values in DAX Commands and Tips

Creating a dynamic summarization table based on measure values

HeevaCh — Wed, 15 May 2024 12:07:05 GMT

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"))))))))))))))))

Re: Creating a dynamic summarization table based on measure values

xifeng_L — Wed, 15 May 2024 15:08:37 GMT

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~

Re: Creating a dynamic summarization table based on measure values

HeevaCh — Thu, 16 May 2024 06:44:23 GMT

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.

Re: Creating a dynamic summarization table based on measure values

HeevaCh — Thu, 16 May 2024 06:49:36 GMT

Thanks!