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

 

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

 

Accounting PeriodIDContractBand Cost 
30/06/20241AX         110.00
30/06/20242BY     1,050.00
30/11/20233BZ         500.00
30/11/20231CZ         750.00
30/11/20232CZ         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:

 

  CostCost
ContractBand30/06/202430/11/2023
AX       110.00 
BY     1,050.00 
BZ        500.00
CZ        850.00

 

Instead, I want it to look like this:

 

  CostCost
ContractBand30/06/202430/11/2023
AX       110.00       750.00
BY     1,050.00       600.00
BZ  
CZ  

 

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?

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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.

  • mikemagill's avatar
    mikemagill
    Helper I
    1 year ago

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

     

      CostCost
    ContractBand30/06/202430/11/2023
    AX       110.00       750.00
    BY     1,050.00       600.00
    BZ  
    CZ  

     

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

      • mikemagill's avatar
        mikemagill
        Helper I
        1 year ago

        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?

  • 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