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