Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HeevaCh
Frequent Visitor

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
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"))))))))))))))))
1 ACCEPTED SOLUTION
xifeng_L
Solution Sage
Solution Sage

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~

 

 

View solution in original post

3 REPLIES 3
HeevaCh
Frequent Visitor

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
Solution Sage
Solution Sage

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~

 

 

Thanks!

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.