Measure gets duplicated when used in matrix value

[Anonymous]

Hi, I'd appreciaet your advice

I have 3 SharePoint lists (parent-chil) I pulled them into PBI like a table and created relationship in data model
the $$ amounts are related to parent not the child, so basically users enter their total domain cost and then provide some domain breakdown, but when I create a matrix and a measure of sum, the measure gets duplicated for every single sub domain.

how can I prevent that?

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a measure as follow:

amount = 
var _res=IF (
    MAX ( 'Sub-Domain'[Sub-Domain] )
        = CALCULATE (
            MIN ( 'Sub-Domain'[Sub-Domain] ),
            FILTER (
                ALL ( 'Sub-Domain' ),
                [Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
            )
        ),
    CALCULATE (
        SUM ( 'Sub-Domain'[Amount $] ),
        FILTER (
            ALL ( 'Sub-Domain' ),
            [Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
        )
    )
)
return IF(ISINSCOPE('Main_Table'[Main Table ID]),_res,SUM('Sub-Domain'[Amount $]
))

The final show：

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Don't rely on the implicit measures. Write a custom measure that handles the computations according to your requirements.