- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Power BI Matrix Report

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Power BI Matrix Report

11-11-2022
04:21 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-17-2022
10:20 AM

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

Proud to be a Super User!

Paul on Linkedin.

34 REPLIES 34

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-22-2022
01:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-11-2022
04:50 AM

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

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-13-2022
11:08 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-14-2022
03:47 AM

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

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-14-2022
06:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-14-2022
08:28 AM

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

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-14-2022
10:41 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-15-2022
11:27 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-11-2022
04:45 AM

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

Microsoft Power BI Learning Resources, 2023 !!

Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-13-2022
11:01 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-17-2022
10:20 AM

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

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
01:53 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
03:43 AM

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

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
03:51 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
02:01 AM

I'm glad you have recovered!

As regards you last request, is the data you are referring to the same as the sample file?

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
02:04 AM

Hi @PaulDBrown , Thanks for your gesture!!

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

Thanks

A

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
02:10 AM

Another question..

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

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
02:17 AM

Hi @PaulDBrown ,

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

Thanks

A

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
04:13 AM

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

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2022
04:52 AM

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

Announcements

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Check out the February 2024 Power BI update to learn about new features.

Featured Topics

Top Solution Authors

User | Count |
---|---|

157 | |

113 | |

100 | |

96 | |

67 |

Top Kudoed Authors

User | Count |
---|---|

254 | |

176 | |

140 | |

94 | |

81 |