Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Icon for Helper III rankHelper 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 ...
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

     


     

amsrivastavaa's avatar
amsrivastavaa
Icon for Helper III rankHelper 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
Icon for Community Champion rankCommunity 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])