Forum Discussion

HeevaCh's avatar
HeevaCh
Frequent Visitor
2 years ago
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.

ClientStatus
AKey
BUncertain
C Uncertain
DFrequent
EKey
FKey
GFrequent
HKey
ISpender
JSpender
KFrequent
LUncertain

 I want a summarization table that shows something like this.

Status# Clients
Key4
Uncertain3
Frequent3
Spender2

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"))))))))))))))))
"category"
"Dynamic Slicer"
"MEASURE"
DAX SUMMARIZE
segmentation
SUMMARIZE
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    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~

     

     

3 Replies

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    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's avatar
    HeevaCh
    Frequent Visitor
    2 years ago

    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.