Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago
Solved

Power BI Matrix Report

Hi Guys!!   I have data as below    Type Project Year Amount Credit P-1 2017 10 Credit P-2 2017 20 Credit P-1 2018 30 Credit P-2 2018 40 Credit P-3 2018 50 ...
  • 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