Power BI Matrix Report

[amsrivastavaa]

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
Credit	P-1	2019	60

Ideally, If will create Matrix Visuals on the above data, it looks like as below

Type	2017	2018	2019
Credit	30	120	60

But I need to create Matrix report where Sum of Amount will be consider only for those Project which are common to previous Year projects, i.e.

As there are two Projects (P-1 and P-2) which are common to Year 2017 and 2018, i want to have only Amount for these two project, so in this case For 2017, Amount for Year 2017 is 10 and 20 for Project P-1 and P-2 and these too are avaialble in 2018, so it will 10+20 =30 for Year 2017.

For 2018, though it has total three project (P-1, P-2 and P-3) where summation of Amount = 30+40+50=120 but I want to display data only for P-1 and P-2 as these are commmon to previous year, so in this case in will be 30+40=70.

And so on for 2019, i.e. only Year P-1 for Year 2019.

Outcome required is : 

Type	2017	2018	2019
Credit	30	70	60

Note : As per requirement, SLicer for Project is not allowed to create.

Thnks

Amit 

[v-henryk-mstf]

Hi @amsrivastavaa ,

Whether the advice given by @PaulDBrown  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[PaulDBrown]

Try this measure (my table is 'fTable'):

In previous year =
VAR _CYProjects =
    VALUES ( fTable[Project] )
VAR _PYProjects =
    CALCULATETABLE (
        VALUES ( fTable[Project] ),
        FILTER ( ALL ( fTable ), fTable[Year] = MAX ( fTable[Year] ) - 1 )
    )
RETURN
    CALCULATE ( SUM ( fTable[Amount] ), INTERSECT ( _CYProjects, _PYProjects ) )

[amsrivastavaa]

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]

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]

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]

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]

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

[amsrivastavaa]

Hi @PaulDBrown ,

Any update on above mentioned query, Is this is doable?

Thanks

A

[amitchandak]

@amsrivastavaa , Try a measure like

Measure = var _tab = SUMMARIZE(FILTER(ALLSELECTED(Data2), Data2[Year] = max(Data2[Year])-1), Data2[Project])
return
CALCULATE(SUM(Data2[Amount]), FILTER(Data2, Data2[Project] in _tab || ISBLANK(COUNTX(_tab,[Project]))))

[amsrivastavaa]

HI @amitchandak , Thanks for the reply.

I have placed PBIX at below URL with my requirement.

URL : https://1drv.ms/u/s!Ahtm7otFIxr8c5QlXvBfzm_pCHs?e=PDtuUk

I have incorporated above in the same PBIX and detailed my requirement as well.

Please provide your valuable suggestion.

Thanks

A.

[PaulDBrown · Accepted solution]

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]

Hi @PaulDBrown ,

Apologies for reverting late!! Was out of work becasue of Bad health.

Anyways, Today, I have gone through the solution and it is working well, it is just an example of your excellence!!

I have few very little requirement on top of that!! May be, I am asking a bit more, but thats how things are

1. I want to show Total number of distinct Project used across matrix based on Year selected.

For, e.g. If user selected say Year = 2018, 2019 and 2020 and I have data as below 

Type	2018	2019	2020
Credit	566	454	4545
Debit	343	45	34

Let say for Credit, Total number of distinct project used are acorss years (For 2018 : #project =2 ; For 2019 : #project =2 ;For 2020 : #project =2 ) i.e. 2

But,for Debit, Total number of distinct project used are acorss years (For 2018 : #project =3 ; For 2019 : #project =3 ;For 2020 : #project =3 ) i.e. 3

So, I want to display TOTAL NUMBER OF DISTINCT PROJECT on the CARD, i.e 3 (as this is maximum)

2. Lets say, User selected Type=Credit in FILTER, then need to display TOTAL NUMBER OF DISTINCT PROJECT on the CARD, i.e 2

3. Also, if there is another filter say, Model, and User selected Model=A, then, in available data, what ever will be TOTAL NUMBER OF DISTINCT PROJECT on the CARD, are will get display over the CARD.

4. I want to include ToolTip on every entry of amout that will say number of project used for particular year for particular TYPE.

i.e. If user hovers around 566 value, i,e. Type is Credit and Year is 2018, then tool tip will show Number of project as 2.

Please suggest!!

Thanks

A

[PaulDBrown]

For the tooltip, do you just need the number or the number & the list of projects?

[amsrivastavaa]

Hi @PaulDBrown ,

I need just a distinct count of project for that year and its previous year only.

When user hover on amount of Year=2018 and Type=Credit , then he/she can see data something like this.

	2017	2018
Number of Project	2	2

Thanks

A

[PaulDBrown]

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]

Hi @PaulDBrown , Thanks for your gesture!!

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

Thanks

A

[PaulDBrown]

Another question..

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

[amsrivastavaa]

Hi @PaulDBrown ,

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

Thanks

A

[PaulDBrown]

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

[amsrivastavaa]

Hi @PaulDBrown ,

Unfortunatly, its not working as expected, it loosing basic functionality of common project for all the year (which is detailed above and you have guided me for the solution already)

So, implemenetion of these measure will be on top of that functionality of common project criteria.

I have detailed my requirement in PBIX under Today's date tab, please find the link for the same.

https://1drv.ms/u/s!Ahtm7otFIxr8d75HKWwpAvZcKBU?e=minbTX

Please suggest!!

Thanks

A