Help with complex measure for Matrix Visual

[mikemagill]

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 anyone help?

I have a large table (approximately 30 million rows). The structure is similar to the example data below:

Accounting Period	ID	Contract	Band	Cost
30/06/2024	1	A	X	110.00
30/06/2024	2	B	Y	1,050.00
30/11/2023	3	B	Z	500.00
30/11/2023	1	C	Z	750.00
30/11/2023	2	C	Z	100.00

I want to create a matrix visual with Contract and Band as rows, two user-specified accounting periods as the columns and total cost as the values. I can manage this part but instead of the aggregation in the earlier of the two accounting periods being on the basis of Contract and Band, I need it to be on the basis of the ID in the later accounting period.

The normal matrix visual using the data above would look similar to this:

		Cost	Cost
Contract	Band	30/06/2024	30/11/2023
A	X	110.00
B	Y	1,050.00
B	Z		500.00
C	Z		850.00

Instead, I want it to look like this:

		Cost	Cost
Contract	Band	30/06/2024	30/11/2023
A	X	110.00	750.00
B	Y	1,050.00	600.00
B	Z
C	Z

In other words, I want it to ignore the Contract and Band and aggregate based on the IDs.  It's very hard to explain but I hope the example helps.

Bearing in mind the size of the table, can anyone help me create the measure?

[Anonymous]

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]

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

[mikemagill]

Sorry, my example table was wrong - It's been a long week and it's only Monday!

		Cost	Cost
Contract	Band	30/06/2024	30/11/2023
A	X	110.00	750.00
B	Y	1,050.00	600.00
B	Z
C	Z

Does that make more sense now?  Thanks for reaching out.

[Greg_Deckler]

@mikemagill Is the B Z row in the original data supposed to have an ID of 2?

[mikemagill]

No.  I have deliberately included a third ID (3) to show that there could be cost allocated to IDs in the earlier month that don't exist in the later month.

Therefore, the 600 is Nov-23 is made up of 100 from ID2 and 500 from ID3.

Does that make sense?

[Greg_Deckler]

@mikemagill This isn't making much sense to me. The ID is different for the 2 C Z rows so if you are aggregating based on ID while ignoring Contract and Band, that line of 850 seems to be the exact opposite of what you are stating.