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

     


     

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])

  • amsrivastavaa's avatar
    amsrivastavaa
    Helper III
    3 years ago

    Hi PaulDBrown , Thanks!!! Its working as expected.

     

    Just a little more help needed from you!!

     

    I need to display Growth Rate with respect to previous year selected in the canvas for the common projects available across the year.

     

    So, after having Common Project logic, I need to display Growth Rate across year as detailed below :

     

    1. Lets say user selected YEAR 2017, 2018 and 2019, then I want to show growth rate as below

     

     

    i.e. For 2017 - as this is least year, so it will be 0

         For 2018 = (Actual value of 2018 - Actual value of 2017)/Actual Value of 2017 

                        = (30-10)/10 = 2

         For 2019 = (Actual value of 2019- Actual value of 2018)/Actual Value of 2018 

                        = (60-30)/30 = 1

     

    2. Lets say User selected year 2017 and 2019, then growth rate need to be calculated  as shown below : 

     

    i.e. 2017 : earliest year , so 0

       For 2019 = (Actual value of 2019 - Actual value of 2017)/Actual Value of 2017 

                        = (60-10)/10 = 5

     

    3. If lets say user selected year 2018, 2020 and 2022, then growth rate will be calcauted as below 

    i.e. 2018 : earliest year , so 0

       For 2020 = (Actual value of 2020 - Actual value of 2018)/Actual Value of 2018 

       For 2022 = (Actual value of 2022 - Actual value of 2020)/Actual Value of 2020

     

    Please suggest !!

     

    Thanks

    A 

                        

     

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      3 years ago

      This should work. FIrst of all, I've added a Year dimension table to make things easier:

      Then a new temp measure:

       

      PY Temp =
VAR _PY =
    CALCULATE (
        MAX ( 'Year Table'[dYear] ),
        FILTER (
            ALLSELECTED ( 'Year Table'[dYear] ),
            'Year Table'[dYear] < MAX ( 'Year Table'[dYear] )
        )
    )
VAR _PYValue =
    CALCULATE (
        SUM ( fTable[Amount] ),
        FILTER ( ALL ( 'Year Table' ), 'Year Table'[dYear] = _PY )
    )
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 )
    )
RETURN
    IF ( MAX ( fTable[Project] ) IN _CP, _PYValue )

       

      and the final Growth measure:

       

      Growth Rate = 
VAR _PYValue = SUMX(ADDCOLUMNS(VALUES(fTable[Project]), "@PY", [PY Temp]), [@PY])
VAR _result = DIVIDE([Common Projects]- _PYValue, _PYValue) 
RETURN
COALESCE(_result, 0)

       

      Sample PBIX file attached

      • amsrivastavaa's avatar
        amsrivastavaa
        Helper III
        3 years ago

        Hi PaulDBrown , Thanks for the reply.

         

        It's working well but with few exception.

         

        1. I have introduced DEBIT as a type in the data and introduced Type filter in the report canvas, then, I f I am selecting multiple values i.e. both Debit and Credit, its not showing current data.

         

        2. I have introduced another column in the Data say Model and introduced Model as a slicer, then its not working as expected.

         

        3. Also, Growth Rate will show data only for those projects which are common to all years selected.

         

        3.1 

        Lets Say User selected Year 2017, 2018 and 2019.

        And, P-1 is available for Year 2017 and 2019

        And P-2 is available for Year 2018 and 2019

        And P-3 is avilable for Year 2017 and 2018

         

        Then, as no project is common in all the year, it grwoth rate matrix should not show any data.

         

        3.2

        Lets Say User selected Year 2017, 2018 and 2019.

        And, P-1 is available for Year 2017,2018 and 2019

        And P-2 is available for Year 2018 and 2019

        And P-3 is avilable for Year 2017 and 2019

         

        Then, as P-1 is available for all the projects, it must be considered and Growth Matrix will display its data. 

         

        I am sharing PBIX URL and i have detailed issue in detail.

         

        https://1drv.ms/u/s!Ahtm7otFIxr8duY8CzdqQr0ybsA?e=RpITZo

         

         

         

         

         

        Please suggest, that would be very helpful.

         

        Thanks

        A