Forum Discussion

DanDan's avatar
DanDan
Regular Visitor
5 years ago
Solved

Removing repeated values in matrix

Apologies if this is a bit basic and my Subject title is vague - I'm a strong beginner at best!

 

I've two matrices and a filter with 'Budget' selected. The difference between the matrices is that in the second instead of using the implicit measure 'Value' from BudgetCosts (which is summed) I've used an explicit measure Budget Cost = SUM(BudgetCosts[Value])

 

Problem: For the row field, Item Description, I want to see values only in the Amount column (i.e. the amounts relating to the items from the ActualsCosts table) and nothing in the Value/Budget Cost columns - as these values from the BudFctCost table should relate only to Account Code (i.e. Line Item) and Supplier ID (i.e. Supplier Name). I was hoping that maybe this can be resolved through DAX...

 

Questions:

  • Matrix 1 - how do I remove the repeated values for the Item Description in the Values column?
  • Matrix 2 - how do I remove both the repeated values in Budget Cost column and the items where there is nothing in Amount column (i.e. Item 1, Item 10, Item 12 etc)? In fact why do they appear in the first place when I use an explicit measure that presumably is doing the same as the implicit measure?

 

Thanks for any help!

 

Matrix 1, Filter, Matrix 2, Fields (Matrix 1):

 

Data Model and Relationships:

 

 

 

 

 

 

Lookup Tables (Mapping and Supplier):

 

Fact Tables (ActualsCosts and BudFctCost):

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi DanDan ï¼Œ

    You can update the measure [Budget Cost ] as below and create another new measure to replace it on the matrix, please find the details in the attachment.

    Budget Cost = 
IF (
    (
        ISINSCOPE ( 'Mapping'[Line Item] )
            && NOT ( ISINSCOPE ( 'ActualsCosts'[Item Description] ) )
    )
        || (
            NOT ( ISINSCOPE ( 'ActualsCosts'[Item Description] ) )
                && ISINSCOPE ( 'Supplier'[Supplier Name] )
        ),
    SUM ( BudgetCosts[Value] ),
    BLANK ()
)
    Measure = SUMX(VALUES('Mapping'[Line Item]),[Budget Cost])

    Best Regards

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi DanDan ï¼Œ

    You can update the measure [Budget Cost ] as below and create another new measure to replace it on the matrix, please find the details in the attachment.

    Budget Cost = 
IF (
    (
        ISINSCOPE ( 'Mapping'[Line Item] )
            && NOT ( ISINSCOPE ( 'ActualsCosts'[Item Description] ) )
    )
        || (
            NOT ( ISINSCOPE ( 'ActualsCosts'[Item Description] ) )
                && ISINSCOPE ( 'Supplier'[Supplier Name] )
        ),
    SUM ( BudgetCosts[Value] ),
    BLANK ()
)
    Measure = SUMX(VALUES('Mapping'[Line Item]),[Budget Cost])

    Best Regards

    • DanDan's avatar
      DanDan
      Regular Visitor
      5 years ago

      That's amazing! It works perfectly. Thanks so much Anonymous - what a Pro! ğŸ˜€

       

      ISINSCOPE - I need to learn this!

       

      Very much appreciated,

      Dan

    • FarrukhData's avatar
      FarrukhData
      Frequent Visitor
      1 year ago

      Hi,
      what if we can't sum because we have text repeating?

      orange is rows and black is values. I don't want black to repeat.