Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
amsrivastavaa
Helper III
Helper III

Power BI Matrix Report

Hi Guys!!

 

I have data as below 

 

TypeProjectYearAmount
CreditP-1201710
CreditP-2201720
CreditP-1201830
CreditP-2201840
CreditP-3201850
CreditP-1201960

 

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

Type201720182019
Credit3012060

 

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 : 

Type201720182019
Credit307060

 

 

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

 

Thnks

Amit 

 

1 ACCEPTED SOLUTION

See if this works for you.

First the model

model.jpg

 

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:

PyP.gif

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

rep 3.jpg

 

 

 

Sample file attached

 


 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

34 REPLIES 34
v-henryk-mstf
Community Support
Community Support

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
Community Champion
Community Champion

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

Result.jpg





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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 .

 

amsrivastavaa_3-1668409645510.png

 

2. When year selected are 2017 and 2019 only, as only P-1 is common, so data for P-1 will only be considered

 

amsrivastavaa_1-1668409464974.png

 

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

 

amsrivastavaa_2-1668409494958.png

 

Please suggest!!

 

Thanks

A

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

CP.gif





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

amsrivastavaa_0-1668437370528.png

 

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 : 

 

amsrivastavaa_1-1668437602476.png

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

                    

 

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

model.jpgThen 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)

 

PY Projects.gif

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

Hi @PaulDBrown ,

 

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

 

Thanks

A

amitchandak
Super User
Super User

@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]))))
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.

 

 

See if this works for you.

First the model

model.jpg

 

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:

PyP.gif

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

rep 3.jpg

 

 

 

Sample file attached

 


 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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 

 

Type201820192020
Credit5664544545
Debit3434534

 

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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

 

 20172018
Number of Project22

 

 

Thanks

A

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @PaulDBrown , Thanks for your gesture!!

 

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

 

Thanks

A

Another question..

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @PaulDBrown ,

 

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

 

Thanks

A

 

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.

DC tooltip.jpg

and you will get the following:

DC gif.gif

Sample file attached

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.