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 ,
Many Thanks for the solution !!!
I have implemented in my solution and so far it passes all the test cases, I need to do few more testing on the same.
Meanwhile, could you please help me out on below concern.
Power BI Matrix with Color Coding - Microsoft Power BI Community
I have raised this in the community also.
Just a glimpses of the query.
This time, I need to show Data for Average value against same TYPE and YEAR for all the project, here i dont need to filter the data based on common project available for all the project.
I am able to do it till one point however i got stuck with color coding of the column.
I am not able to pick value of last year available in selected matrix data.
Please find complete detail over the above link and help me out on the same as well
Thanks
A
Hi PaulDBrown ,
Just to add on, I here by sharing PBIX file with my issue detailed.
https://1drv.ms/u/s!Ahtm7otFIxr8fYyZ9J_MyZCOkMA?e=yTo7Vt
Last solution which you have suggested, it looks good and passed all the Test cases so far however I am still testing for few more, will let you know accordingly.
However, in another page of same report, I need to perform similar activity but with all the project avaialble (not looking for common project this time).
I am able to create report but got stuck in COLOR Coding part.
Please refer Page Name (12 DEC - Req -1 and 12 DEC - Req-2) in above provided PBIX.
Thanks
A
- amsrivastavaa3 years agoHelper III
Hi PaulDBrown ,
Could you please help me on the above query as well!!
Also, I need to implement same color coding logic for Common Project Growth Rate report,
Report URL : https://1drv.ms/u/s!Ahtm7otFIxr8f5rq6iGfIo5IlEo?e=azauXq
i.e. if that YEAR growth rate > last available year Growth rate ==> GREEN
if that YEAR growth rate < last available year Growth rate ==> RED,
if that YEAR growth rate = last available year Growth rate ==> BLACK
PaulDBrown : Please suggest !!!
Thanks
A