Removing repeated values in matrix

[DanDan]

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):

[v-yiruan-msft · Accepted solution]

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]

That's amazing! It works perfectly. Thanks so much @v-yiruan-msft - what a Pro! 😀

ISINSCOPE - I need to learn this!

Very much appreciated,

Dan