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 ...
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

     


     

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

I'm glad you have recovered! 
As regards you last request, is the data you are referring to the same as the sample file?

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

Hi PaulDBrown , Thanks for your gesture!!

 

Yes, sample file is same as you have shared last time with me.

 

Thanks

A

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Another question..

    Do you need the distinct count over all projects or only of those which are persent in the selected years?

    • amsrivastavaa's avatar
      amsrivastavaa
      Helper III
      3 years ago

      Hi PaulDBrown ,

       

      Only for the data, which are available after appling all the filters including YEAR, TYPE, etc.

       

      Thanks

      A

       

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        3 years ago

        Ok see if this works for you. 

        New measures:

        For the matrix

        Sum Amount = 
SUM(fTable[Amount])

        For the tooltip page

        Distinctcount Projects = 
DISTINCTCOUNT(fTable[Project])

        For the card

        Max distinctcount =
MAXX (
    ADDCOLUMNS ( VALUES ( 'Type Table'[Type] ), "@DC", [Distinctcount Projects] ),
    [@DC]
)

        Set up the tooltip page with the [Distinctcount projects] measure (I changed the name for the visual) and add the 'Type Table'[Type] and the 'Year Table'[dYear] as the filters.

        and you will get the following:

        Sample file attached