Forum Discussion
Power BI Matrix Report
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
Try with these two measures:
As a step for the final measure
PY =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
fTable,
'Type Table'[Type],
'Project Table'[Project],
'Model Table'[Model]
),
"@PY", [PY Temp]
),
[@PY]
)
Growth Rate with Net Profit =
VAR _C = CALCULATE([PY], 'Type Table'[Type] = "Credit")
VAR _D = CALCULATE([PY], 'Type Table'[Type] = "Debit")
VAR _PYNP = DIVIDE((_C - _D), _C)
VAR _GRNP = VALUE([Comon Projects with Net Profit])
VAR _NP = IF(AND(_C = 0, _D = 0), 0, DIVIDE(_GRNP, _PYNP) -1)
RETURN
IF(MAX('Type Table'[Type]) = "Net Profit", _NP, [Growth Rate])
Sample PBIX 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
Change the [Growth Rate with Net Profit} to:
Growth Rate with Net Profit = VAR _C = CALCULATE ( [PY], FILTER ( ALL ( 'Type' ), 'Type'[Type] = "Credit" ) ) VAR _D = CALCULATE ( [PY], FILTER ( ALL ( 'Type' ), 'Type'[Type] = "Debit" ) ) VAR _PYNP = DIVIDE ( ( _C - _D ), _C ) VAR _GRNP = VALUE ( [Comon Projects with Net Profit] ) VAR _NP = IF ( AND ( _C = 0, _D = 0 ), 0, DIVIDE ( _GRNP, _PYNP ) - 1 ) RETURN IF ( MAX ( 'Type'[Type] ) = "Net Profit", _NP, [Growth Rate] )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
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