fitler data

[razieh1990]

Hello 

I appreciate it if you help me.

I have a dataset including all the sales from 2018. 

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:

 

[Anonymous]

Hi @razieh1990 ,

Base on your description, it seems that you want to create a matrix visual. And when you select the year 2020, it will display the target values for the selected year(s) with the months. For the left years, it will display the sales with the left years. Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[razieh1990]

Hello , 

here is the power bi file I tried to explain my logic and what I want in this power bi file . I appreciate your time https://we.tl/t-0e0eRtGnjY 

[Anonymous · Accepted solution]

Hi @razieh1990 ,

I updated your sample pbix file(see the attachment), please check if that is what you want.

MSales = 
VAR _years =
    ALLSELECTED ( 'Years'[Year] )
VAR _year =
    SELECTEDVALUE ( 'Data'[Year] )
VAR _month =
    SELECTEDVALUE ( 'Months'[Month] )
RETURN
    IF (
        _year
            IN _years && ISINSCOPE ( 'Months'[Month] ),
        SUM ( Data[Sale] ),
        IF (
            NOT ( _year IN _years )
                && ISINSCOPE ( 'Data'[Year] )
                && NOT ( ISINSCOPE ( 'Months'[Month] ) ),
            SUM ( 'Data'[Sale] ),
            BLANK ()
        )
    )

MTarget = 
VAR _years =
    ALLSELECTED ( 'Years'[Year] )
RETURN
    IF (
        SELECTEDVALUE ( 'Data'[Year] )
            IN _years && ISINSCOPE ( 'Data'[Year] )
                && NOT ( ISINSCOPE ( 'Months'[Month] ) ),
        SUM ( 'Data'[Target] )
    )

Best Regards