Two value in one row in matrix

Good morning, I have a situation like this: and I'm replicating it as a matrix. But I was asked if I can change the view to this: how can i put two values in a field aligned ...

Need Help

v-alq-msft

5 years ago

Hi, Anonymous

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

KPI:

Then you need to make 'KPI1', 'KPI2', 'KPI3' selected and unpivot them.

There is no relationship between tables. You may create a measure as below.

Result = 
var t = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT('KPI'[New KPI]),
        DISTINCT('Table'[Year])
    ),
    "Result",
    IF(
        [New KPI]="KPI1",
        CONCATENATEX(
            FILTER(
                ALL('Table'),
                [KPI] in {"KPI1","KPI2"}&&
                [Year]=EARLIER('Table'[Year])
            ),
            [Value],
            UNICHAR(10)
        ),
        CONCATENATEX(
            FILTER(
                ALL('Table'),
                [KPI]=EARLIER('KPI'[New KPI])&&
                [Year]=EARLIER('Table'[Year])
            ),
            [Value],
            UNICHAR(10)
        )
    )
)
return
CONCATENATEX(
    t,
    [Result],
    UNICHAR(10)
)

Result: