Avoiding displaying duplicate values for parent child items in a matrix

[tnwcloud]

I have a dataset that includes goals and multiple options for each goal. Each goal has a target value and each option has an estimate. There is a one to many relationship between goal and option. Trying to figure out how not to repeat the 'Goal Target' for each Option in the simplified matrix below. Basically I want the opposite of Don't Show Subtotals, where I show the Subtotal of 'Goal Target' for each Goal but don't show the lime item for each Option. Putting this under DAX but would welcome data model or visual option solutions.

Can't figure out how to upload a file so...

 

[DataInsights · Accepted solution]

@tnwcloud,

The May 2024 release of Power BI Desktop contains matrix enhancements including various layout options. Here's a link with details:

https://powerbi.microsoft.com/en-us/blog/power-bi-may-2024-feature-summary/#post-27048-_Toc167109007 

[Anonymous · Accepted solution]

Hi @tnwcloud 

Please try the following measure:

Goal Target = 
IF(
    NOT(HASONEVALUE('Options'[OptionID])),
    CALCULATE(
        SUM('Goals'[Target]),
        CROSSFILTER('Goals'[GoalID], 'Options'[GoalID], Both)
    ),
    BLANK()
)

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights · Accepted solution]

@tnwcloud,

The May 2024 release of Power BI Desktop contains matrix enhancements including various layout options. Here's a link with details:

https://powerbi.microsoft.com/en-us/blog/power-bi-may-2024-feature-summary/#post-27048-_Toc167109007