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