Forum Discussion

razieh1990's avatar
razieh1990
Helper I
2 years ago
Solved

fitler data

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 selecti...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
razieh1990
Helper I
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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