Forum Discussion

mikemagill's avatar
mikemagill
Helper I
1 year ago

Help with complex measure for Matrix Visual

I have a complex matrix visual requirement that I'm struggling with.  I have managed to solve it using a calculated table but this has limitations that a solution using measures would not have.  Can ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi mikemagill 

 

According to your description, I should be able to understand that your two needs are to add the Cost value with ID 3 to ID 2 in the Matrix, and to have Cost ignore Contract and Band showing up in the same row.

However, based on my testing, it seems difficult to implement, only with the ID as the only field in the Matrix Rows.

Create the following measures:

 

TotalCost = CALCULATE(SUM('DataTable'[ Cost ]), ALLEXCEPT('DataTable', 'DataTable'[ID], 'DataTable'[Accounting Period]))
AdjustedCost = 
VAR CostID2 = CALCULATE([TotalCost], 'DataTable'[ID] = 2)
VAR CostID3 = CALCULATE([TotalCost], 'DataTable'[ID] = 3)
RETURN
IF(MAX('DataTable'[ID]) = 2, CostID2 + CostID3, [TotalCost])

 

Create a Matrix visual and select "ID is not 3" in the visual filtering.

The Matrix visual is like this:

When the ID as the only field in the Matrix Rows. It like this:

 

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

  • mikemagill's avatar
    mikemagill
    Helper I
    1 year ago

    Hi.  Thanks for the input.  I've been working some more on this and have made a bit of progress but there are other complexities that I didn't share and that have prompted me to give up.

     

    If it helps anyone else, here is the measure I arrived at:

     

    Comparative Cost =

     

    VAR CurrentPeriod =
        MAX('Date Table - Accounting (Disconnected 01)'[End of Month])

     

    VAR ComparativePeriod =
        MAX('Date Table - Accounting (Disconnected 02)'[End of Month])

     

    VAR IDs =
        CALCULATETABLE(
            DISTINCT('Forecast Data'[ID]),
            'Forecast Data'[Accounting Period] = CurrentPeriod
        )

     

    VAR ALLIDs =
        CALCULATETABLE(
            DISTINCT('Forecast Data'[ID]),
            ALL('Forecast Data'),
            'Forecast Data'[Accounting Period] = CurrentPeriod
        )

     

    VAR IDsInBoth =
        CALCULATE(
            SUM('Forecast Data'[Cost]),
            ALL('Forecast Data'),
            'Forecast Data'[Accounting Period] = ComparativePeriod,
            TREATAS(IDs, 'Forecast Data'[ID])
        )

     

    VAR IDsOnlyInComp =
        CALCULATE(
            SUM('Forecast Data'[Cost]),
            KEEPFILTERS('Forecast Data'),
            'Forecast Data'[Accounting Period] = ComparativePeriod,
            NOT 'Forecast Data'[ID] IN ALLIDs
        )

     

    VAR Result = IDsInBoth + IDsOnlyInComp

     

    RETURN

     

        Result