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 solution.
The above solution works well atmost for most of the cases but it fails in one scenario, i am detailing the same as below.
Changes Made by me :
1. I have replaced TYPE TABLE with TYPE where (now) I have two columns TYPE and Index.
2. I have changed all the reference of TYPE TABLE to TYPE as well as in the DATA model, till this points everything works well and removed the TYPE TABLE from the model.
Issue :
It works well until I haven't sort the TYPE in matrix based on Index column.
I tried to SORT the TYPE in matrix via INDEX column, as soon as I did that, NET PROFIT start showing ZERO for all the records as shown below in GROWTH RATE MATRIX
Below is how i tried to sort the Type in matrix based on Index value.
Again when i changed the sorting to TYPE, it starts showing me values.
Please suggest how to get rid of this issue.
I hereby providing link for PBIX
Latest version you have shared :
https://1drv.ms/u/s!Ahtm7otFIxr8e3RTNx4p3hSei34?e=fCblRF
Version where i have made above changes :
https://1drv.ms/u/s!Ahtm7otFIxr8fMsLohiBJlx1yhc?e=ABG8Az
PaulDBrown -One more information, after sorting, it's giving value as Zero for all the enity which has been calculated at run time like Net Profit.
Please suggest....that would be very helpful.
Thanks
A
HI PaulDBrown ,
Could you please provide your immaculate expertize on the above mentioned query!!!
Thank You
A