Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago
Solved

Power BI Matrix Report

Hi Guys!!

 

I have data as below 

 

TypeProjectYearAmount
CreditP-1201710
CreditP-2201720
CreditP-1201830
CreditP-2201840
CreditP-3201850
CreditP-1201960

 

Ideally, If will create Matrix Visuals on the above data, it looks like as below

Type201720182019
Credit3012060

 

But I need to create Matrix report where Sum of Amount will be consider only for those Project which are common to previous Year projects, i.e.

 

As there are two Projects (P-1 and P-2) which are common to Year 2017 and 2018, i want to have only Amount for these two project, so in this case For 2017, Amount for Year 2017 is 10 and 20 for Project P-1 and P-2 and these too are avaialble in 2018, so it will 10+20 =30 for Year 2017.

 

For 2018, though it has total three project (P-1, P-2 and P-3) where summation of Amount = 30+40+50=120 but I want to display data only for P-1 and P-2 as these are commmon to previous year, so in this case in will be 30+40=70.

 

And so on for 2019, i.e. only Year P-1 for Year 2019.

 

Outcome required is : 

Type201720182019
Credit307060

 

 

Note : As per requirement, SLicer for Project is not allowed to create.

 

Thnks

Amit 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    See if this works for you.

    First the model

     

    I've changed the measures to:

    Rows Values Temp =
VAR _CYProjects =
    COUNTROWS ( ALLSELECTED ( 'Year Table'[dYear] ) )
VAR _ALLProjects =
    CALCULATE (
        COUNT ( fTable[Year] ),
        FILTER (
            ALLSELECTED ( fTable ),
            fTable[Project] = MAX ( fTable[Project] )
                && fTable[Type] = MAX ( fTable[Type] )
                && fTable[Model] = MAX ( fTable[Model] )
        )
    )
VAR _CP =
    CALCULATETABLE (
        VALUES ( fTable[Project] ),
        FILTER ( fTable, _CYProjects = _ALLProjects )
    )
VAR _result =
    IF ( MAX ( fTable[Project] ) IN _CP, SUM ( fTable[Amount] ) )
RETURN
    _result

    Common Projects =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            fTable,
            'Type Table'[Type],
            'Project Table'[Project],
            'Model Table'[Model]
        ),
        "@Total", [Rows Values Temp]
    ),
    [@Total]
)

    PY Temp =
VAR _PY =
    CALCULATE (
        MAX ( 'Year Table'[dYear] ),
        FILTER (
            ALLSELECTED ( 'Year Table'[dYear] ),
            'Year Table'[dYear] < MAX ( 'Year Table'[dYear] )
        )
    )
RETURN
    IF (
        ISBLANK ( [Common Projects] ),
        BLANK (),
        CALCULATE (
            SUM ( fTable[Amount] ),
            FILTER ( ALL ( 'Year Table'[dYear] ), 'Year Table'[dYear] = _PY )
        )
    )

    Growth Rate =
VAR _PYValue =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                fTable,
                'Type Table'[Type],
                'Project Table'[Project],
                'Model Table'[Model]
            ),
            "@PY", [PY Temp]
        ),
        [@PY]
    )
VAR _result =
    DIVIDE ( [Common Projects] - _PYValue, _PYValue )
RETURN
    IF (
        OR ( ISBLANK ( SUM ( fTable[Amount] ) ), ISBLANK ( [Common Projects] ) ),
        BLANK (),
        COALESCE ( _result, 0 )
    )

    To get:

    As for you Req-3, you are not getting values for [Common Projects] and [Growth Rate] because thare no projects for model B which are present in all three years selected, according to your brief which says:

    "When User selects all the Year,(Slicer) Amount will be shown only for those Projects which are common to all the Year."

     

     

     

    Sample file attached

     


     

34 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    amsrivastavaa , Try a measure like

     

    Measure = var _tab = SUMMARIZE(FILTER(ALLSELECTED(Data2), Data2[Year] = max(Data2[Year])-1), Data2[Project])
return
CALCULATE(SUM(Data2[Amount]), FILTER(Data2, Data2[Project] in _tab || ISBLANK(COUNTX(_tab,[Project]))))
      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        3 years ago

        See if this works for you.

        First the model

         

        I've changed the measures to:

        Rows Values Temp =
VAR _CYProjects =
    COUNTROWS ( ALLSELECTED ( 'Year Table'[dYear] ) )
VAR _ALLProjects =
    CALCULATE (
        COUNT ( fTable[Year] ),
        FILTER (
            ALLSELECTED ( fTable ),
            fTable[Project] = MAX ( fTable[Project] )
                && fTable[Type] = MAX ( fTable[Type] )
                && fTable[Model] = MAX ( fTable[Model] )
        )
    )
VAR _CP =
    CALCULATETABLE (
        VALUES ( fTable[Project] ),
        FILTER ( fTable, _CYProjects = _ALLProjects )
    )
VAR _result =
    IF ( MAX ( fTable[Project] ) IN _CP, SUM ( fTable[Amount] ) )
RETURN
    _result

        Common Projects =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            fTable,
            'Type Table'[Type],
            'Project Table'[Project],
            'Model Table'[Model]
        ),
        "@Total", [Rows Values Temp]
    ),
    [@Total]
)

        PY Temp =
VAR _PY =
    CALCULATE (
        MAX ( 'Year Table'[dYear] ),
        FILTER (
            ALLSELECTED ( 'Year Table'[dYear] ),
            'Year Table'[dYear] < MAX ( 'Year Table'[dYear] )
        )
    )
RETURN
    IF (
        ISBLANK ( [Common Projects] ),
        BLANK (),
        CALCULATE (
            SUM ( fTable[Amount] ),
            FILTER ( ALL ( 'Year Table'[dYear] ), 'Year Table'[dYear] = _PY )
        )
    )

        Growth Rate =
VAR _PYValue =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                fTable,
                'Type Table'[Type],
                'Project Table'[Project],
                'Model Table'[Model]
            ),
            "@PY", [PY Temp]
        ),
        [@PY]
    )
VAR _result =
    DIVIDE ( [Common Projects] - _PYValue, _PYValue )
RETURN
    IF (
        OR ( ISBLANK ( SUM ( fTable[Amount] ) ), ISBLANK ( [Common Projects] ) ),
        BLANK (),
        COALESCE ( _result, 0 )
    )

        To get:

        As for you Req-3, you are not getting values for [Common Projects] and [Growth Rate] because thare no projects for model B which are present in all three years selected, according to your brief which says:

        "When User selects all the Year,(Slicer) Amount will be shown only for those Projects which are common to all the Year."

         

         

         

        Sample file attached

         


         

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Try this measure (my table is 'fTable'):

    In previous year =
VAR _CYProjects =
    VALUES ( fTable[Project] )
VAR _PYProjects =
    CALCULATETABLE (
        VALUES ( fTable[Project] ),
        FILTER ( ALL ( fTable ), fTable[Year] = MAX ( fTable[Year] ) - 1 )
    )
RETURN
    CALCULATE ( SUM ( fTable[Amount] ), INTERSECT ( _CYProjects, _PYProjects ) )

    • amsrivastavaa's avatar
      amsrivastavaa
      Helper III
      3 years ago

      Hi PaulDBrown ,

       

      Its not working as expcted..my requirement is detailed below 

       

      1. When User selects all the Year,(Slicer) Amount will be shown only for those Projects which are common to all the Year.

       

      I.E. in above case, as P-1 is only common Project across all year then only P-1 data will be considered .

       

       

      2. When year selected are 2017 and 2019 only, as only P-1 is common, so data for P-1 will only be considered

       

       

      3- When year selected are 2017 and 2018 only, as P-1 & P-2 are common so data for both P-1 and P-2 will be considered only

       

       

      Please suggest!!

       

      Thanks

      A

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        3 years ago

        See if this works

        A temporary measure to get the correct sum for the common projects:

        Rows Values Temp =
VAR _CYProjects =
    CALCULATE ( DISTINCTCOUNT ( fTable[Year] ), ALLSELECTED ( fTable ) )
VAR _ALLProjects =
    CALCULATE (
        COUNT ( fTable[Year] ),
        FILTER ( ALLSELECTED ( fTable ), fTable[Project] = MAX ( fTable[Project] ) )
    )
VAR _CP =
    CALCULATETABLE (
        VALUES ( fTable[Project] ),
        FILTER ( fTable, _CYProjects = _ALLProjects )
    )
VAR _Value =
    CALCULATE ( SUM ( fTable[Amount] ), _CP )
RETURN
    _Value

        And the final measure for the matrix

        Common Projects = 
SUMX(
    ADDCOLUMNS(
        VALUES(fTable[Project]), "@Sum", [Rows Values Temp]), [@Sum])

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    3 years ago

    Hi amsrivastavaa ,

     

    Whether the advice given by PaulDBrown  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


    Looking forward to your feedback.


    Best Regards,
    Henry