Forum Discussion
Power BI Matrix Report
- 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 _resultCommon 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
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
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
- amsrivastavaa3 years agoHelper III
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
- PaulDBrown3 years agoCommunity Champion
I'm glad you have recovered!
As regards you last request, is the data you are referring to the same as the sample file?- amsrivastavaa3 years agoHelper III
Hi PaulDBrown , Thanks for your gesture!!
Yes, sample file is same as you have shared last time with me.
Thanks
A
- PaulDBrown3 years agoCommunity Champion
For the tooltip, do you just need the number or the number & the list of projects?
- amsrivastavaa3 years agoHelper III
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