data filter

[razieh1990]

Hello 

I appreciate it if you help me.

I have a dataset including all the sales from 2018. and I imported data not direct query.

I want to create a matrix in Power BI similar to the below layout. 

On the page there is a year selection, each year I choose in the slicer, the data for that year should be displayed by month, and also the target should display the target of the selected year and for the rest of the years, the data should be aggregated and displayed yearly.

and the columns should be sorted ascending. for example, if I choose 2022: the layout should be like the matrix below.

for 2024:

for 2022:

[v-xinruzhu-msft · Accepted solution]

Hi @razieh1990 

You can refer to the following sample.

Sample data 

1.Create a type table

2.Create a year table

There is no relationship among the tables.

3.Create a measure

MEASURE =
IF (
    ISFILTERED ( 'Year'[Year] ),
    IF (
        SELECTEDVALUE ( 'Type'[Type] ) = "Target",
        CALCULATE (
            SUM ( 'Table'[Target] ),
            YEAR ( 'Table'[Date] ) = SELECTEDVALUE ( 'Year'[Year] )
        ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            OR (
                FORMAT ( YEAR ( 'Table'[Date] ), "" )
                    IN VALUES ( 'Type'[Type] )
                        && YEAR ( 'Table'[Date] ) <> SELECTEDVALUE ( 'Year'[Year] ),
                YEAR ( 'Table'[Date] ) = SELECTEDVALUE ( 'Year'[Year] )
                    && FORMAT ( 'Table'[Date], "mmmm" ) = SELECTEDVALUE ( 'Type'[Type] )
            )
        )
    ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FORMAT ( YEAR ( 'Table'[Date] ), "" ) IN VALUES ( 'Type'[Type] )
    )
)

4.Put the year field to the slicer, and put the type field and measure to the matrix visual.

Output

Best Regards!

Yolo Zhu

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

[razieh1990]

Thank you very much

[lucadelicio]

Hi,

try to upload on https://wetransfer.com/
the file pbix with the data and upload an image that is visible with the result that you want.
Not a photo of your pc but a screen you can use the windows tool (Win + Caps + S)
Thank you.