Aggregating text measure outputs and using text measure as a legend

[alesya]

Hello 🙂

I have a dataset of different consumer studies where each row is an evaluation of a pair of products by a consumer in a specific study. My goal is to conduct t-tests to identify within each study whether the product wins, loses or is at parity with the competitor product.

Here is a sample :

Study_Pair_Key	Study_ID	Consumer_ID	Product_ID	Comp_Product_ID	Product_Score	Comp_Product_Score
1_HA_CA	1	C1	HA	CA	5	3
1_HA_CB	1	C1	HA	CB	5	4
1_HB_CA	1	C1	HB	CA	1	3
1_HB_CB	1	C1	HB	CB	1	4
1_HA_CA	1	C2	HA	CA	3	6
1_HA_CB	1	C2	HA	CB	3	5
1_HB_CA	1	C2	HB	CA	2	6
1_HB_CB	1	C2	HB	CB	2	5
2_HA_CA	2	C1	HA	CA	4	7
2_HA_CB	2	C1	HA	CB	4	6
2_HB_CA	2	C1	HB	CA	6	7
2_HB_CB	2	C1	HB	CB	6	6
2_HA_CA	2	C2	HA	CA	5	2
2_HA_CB	2	C2	HA	CB	5	3
2_HB_CA	2	C2	HB	CA	3	2
2_HB_CB	2	C2	HB	CB	3	3

I first created a calculated column for the score gap between 2 products:

• Liking_Delta = Table[Product_Score]-Table[Comp_Product_Score]

Then I have created the following measures:

• NoRows = COUNTROWS(Table)

• CriticalValue_measure = T.INV(0.95, [NoRows]-1)

• TTest_measure = DIVIDE(

    AVERAGE(Table[Liking_Delta]),

    DIVIDE(

        STDEV.P(Table[Liking_Delta]),

        SQRT(COUNTROWS('Table'))

    )

)

• WPL_measure =

SWITCH(

    TRUE(),

    [TTest_measure] >= [CriticalValue_measure], "WIN",

    [TTest_measure] <= -[CriticalValue_measure], "LOSS",

    "PARITY"

)

WPL_measure gives me a correct output, and is reactive to filters I use (that I have not given here) when I put it in a matrix visual with the Product_ID and Comp_Product_ID in rows and columns. However, I would like to count the result of each unique Study_Pair_Key and show which represent "WIN", "PARITY" or "LOSS" in a barchart. I understand measures cannot be used in legends, but as I need to basically create a summary of the evaluation results and need this to be dynamic with the different filters(therefore groupings) is there another workaround I could use ? Othwerwise I will have to create a WPL result column in my dataset for each grouping I would like to visualize and this will expand my dataset a lot...

I have browsed the forum and saw similar questions asked,

Re: Use Measure as Legend in Pie Chart or Column i... - Microsoft Fabric Community

Re: Find legend in stacked bar chart using disconn... - Microsoft Fabric Community

Solving Attendance with the Disconnected Table Tri... - Microsoft Fabric Community

But I cannot seem to find any solution to my problem 😞

Any idea how to solve this ?

Thank you so much in advance !!

[v-jialongy-msft]

Hi @alesya 

Instead of expanding your dataset with additional columns for each grouping, you can create a calculated table that dynamically aggregates your WPL_measure results.

WPL_Summary = 
SUMMARIZE(
    'Table',
    'Table'[Study_Pair_Key],
    "WPL_Result", [WPL_measure]
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.