Comparison Table

[Warrior42]

Was looking for some guidance on this.  I have a table full of Model Return data called "Models", from that table I've made two Matricies, with various slicers assigned to each.  The first goal is to get the absolute return data each Matrix based on how you manipulate the slicers, I feel like I have that part figured out.  Although, I was hoping to make a third Matrix that would make a comparison of the two original matricies where the assignment would be based on their Morningstar Category (ie if the category is Moderate they would get matched together).  From there, the new comparison Matrix would have a "Name" column that reads "Model 1 v. Model 2", a Morningstar Category column with the category name they share, and then the return differences for each trailing period (ie 3 mo return of Matrix 1 - 3 mo return of Matrix 2) and fee difference.  Does this  make sense, or is this possible?  Below is a veiw of how I currently have my original Matricies set up, and just looking to get the comp table created underneath them. 

[v-tejrama]

Hi @Warrior42 ,
Thanks for reaching out to the Microsoft fabric community forum.

Working DAX Code Comparison Table 

Comparison Firm = DISTINCT('Models'[Firm Name])

Measure → Return of Selected Model (Model 1)

Return_Model1 =
CALCULATE(
AVERAGE('Models'[Return (%)]),
REMOVEFILTERS('Models'[Firm Name])
)

Purpose: Returns average return for Model 1, selected by slicer on Models.

Measure → Return of Comparison Model (Model 2)

Return_Model2 =
VAR SelectedComparison = SELECTEDVALUE('Comparison Firm'[Firm Name])
RETURN
CALCULATE(
AVERAGE('Models'[Return (%)]),
'Models'[Firm Name] = SelectedComparison
)

Purpose: Returns average return for Model 2, chosen by slicer on Comparision Firm

Measure → Return Difference

Return_Difference = [Return_Model1] - [Return_Model2]

Purpose: Displays the difference between the two selected model returns.

Measure → Comparison Name 

Comparison_Name =
VAR Model1 = SELECTEDVALUE('Models'[Firm Name])
VAR Model2 = SELECTEDVALUE('Comparison Firm'[Firm Name])
RETURN
IF(
ISBLANK(Model1) || ISBLANK(Model2),
BLANK(),
Model1 & " vs " & Model2
)

Purpose: Useful for card visuals showing what you are comparing.

Matrix Visual Setup

Rows - MorningStar Category
Columns - ReturnModel1, ReturnModel2, Return_Difference

Slicers (on the page):

1. Models - For Model 1

2. ComparisionFirm - For Model 2 (Comparison)

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @Warrior42 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you,

Tejaswi.

[DataNinja777]

Hi @Warrior42 ,

What you are looking to create is a classic comparative analysis scenario in Power BI, and it is entirely possible to build. The approach does not involve directly comparing the two visual matrices you have already built, but rather creating a third, new matrix that uses DAX to perform the comparison dynamically. This new matrix will be aware of two separate selections—a primary model group and a comparison model group—and will calculate the differences between them based on their shared Morningstar Category.

To begin, you will need to adjust your data model slightly to allow for an independent comparison selection. The key is to create a disconnected table for any slicer field you wish to use for the comparison group. For instance, if you differentiate the models by 'Firm Name', you would create a new calculated table in Power BI that is not related to your main 'Models' table. This prevents the comparison slicer from filtering your primary selection. You can create this table using a DAX expression.

Comparison Firm = DISTINCT('Models'[Firm Name])

Once this table is created, you can add a slicer to your report using the 'Firm Name' from this new 'Comparison Firm' table. This slicer will be used to choose your second model for the comparison. Next, you will create a series of DAX measures to power the calculations in your final matrix. For each metric you want to compare, such as the '1 Yr' return, you will need measures to find the value for each of the two selected models and then calculate the difference. The measure for your primary model is straightforward, as it will be based on the filters from your primary slicers.

[1Yr Return (Model 1)] = SELECTEDVALUE('Models'[1 Yr])

The measure to get the value for the comparison model is more complex. It must ignore the filter from your primary slicer (e.g., the primary 'Firm Name') and instead apply the selection made in your new 'Comparison Firm' slicer, while still matching on the correct 'Morningstar Category' from the matrix row.

[1Yr Return (Model 2)] = 
VAR SelectedComparisonFirm = SELECTEDVALUE('Comparison Firm'[Firm Name])
RETURN
CALCULATE(
    SELECTEDVALUE('Models'[1 Yr]),
    ALL('Models'[Firm Name]),
    'Models'[Firm Name] = SelectedComparisonFirm,
    TREATAS(VALUES('Models'[Morningstar Category]), 'Models'[Morningstar Category])
)

 With measures for both models, calculating the difference is a simple subtraction.

[1Yr Return Difference] = [1Yr Return (Model 1)] - [1Yr Return (Model 2)]

To generate the "Model 1 v. Model 2" name, you use a similar DAX pattern to retrieve the names from both selections and concatenate them.

[Comparison Name] = 
VAR Model1Name = SELECTEDVALUE('Models'[Name])
VAR SelectedComparisonFirm = SELECTEDVALUE('Comparison Firm'[Firm Name])
VAR Model2Name = 
    CALCULATE(
        SELECTEDVALUE('Models'[Name]),
        ALL('Models'[Firm Name]),
        'Models'[Firm Name] = SelectedComparisonFirm,
        TREATAS(VALUES('Models'[Morningstar Category]), 'Models'[Morningstar Category])
    )
RETURN
IF(
    NOT ISBLANK(Model1Name) && NOT ISBLANK(Model2Name),
    Model1Name & " v. " & Model2Name,
    BLANK()
)

You will need to repeat the process of creating a "Model 2" measure and a "Difference" measure for every return period and fee you wish to analyze. After creating all the necessary measures, you can build your final comparison matrix. Place the Morningstar Category on the rows. Then, add your new measures, such as [Comparison Name] and your various "Difference" measures, to the values field. This matrix will now dynamically display the calculated differences between the two models you select from your primary and comparison slicers.

Best regards,

[Warrior42]

Thank you for all of this.  If I'm following correctly,  I was able to get the new table created with the firm name from the Model table and replaced the current firm name slicer for the second matrix with the firm name from that table.  I also added another firm name slicer to the first table which is based on the Model table.  So I currently have 4 slicers total, the first matrix slicers are based on firm name and model set from the Model table and the other two slicers are based on the firm name from the comparison table and model set from the model table (should I create a second comparison table for those?).   These last two slicers are also linked to the new Matrix.  I've also created the new measures listed below within the ComparisonTable (is that right?)  When I try to create the new comparison matrix I'm not getting anything to populate.

ComparisonFirmTable = Distinct('Models'[Firm Name])

1Yr Return (Model 1) = SELECTEDVALUE('Models'[Total Ret 1 Mo (Qtr-End)])

1Yr Return (Model 2) =

VAR SelectedComparisonFirm = SELECTEDVALUE('ComparisonTable'[1MoReturn_Competitor])

RETURN CALCULATE(

SELECTEDVALUE('Models'[Total Ret 1 Mo (Qtr-End)]),

ALL('Models'[Firm Name]),

'Models'[Firm Name] = SelectedComparisonFirm,

TREATAS(VALUES('Models'[Morningstar Category]), 'Models'[Morningstar Category])

)

1Yr Return Difference = [1Yr Return (Model 1)] - [1Yr Return (Model 2)]

Comparison Name =

VAR Model1Name = SELECTEDVALUE('Models'[Name])

VAR SelectedComparisonFirm = SELECTEDVALUE('ComparisonFirmTable'[Firm Name])

VAR Model2Name = CALCULATE(

SELECTEDVALUE('Models'[Name]),

ALL('Models'[Firm Name]),'Models'[Firm Name] = SelectedComparisonFirm,

TREATAS(VALUES('Models'[Morningstar Category]), 'Models'[Morningstar Category])

)

RETURN IF(

NOT ISBLANK(Model1Name) && NOT ISBLANK(Model2Name),

Model1Name & " v. " & Model2Name,

BLANK()

)