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

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