Forum Discussion
Solved
Creating a dynamic summarization table based on measure values
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 ...
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~
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.