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
Try this measure (my table is 'fTable'):
In previous year =
VAR _CYProjects =
VALUES ( fTable[Project] )
VAR _PYProjects =
CALCULATETABLE (
VALUES ( fTable[Project] ),
FILTER ( ALL ( fTable ), fTable[Year] = MAX ( fTable[Year] ) - 1 )
)
RETURN
CALCULATE ( SUM ( fTable[Amount] ), INTERSECT ( _CYProjects, _PYProjects ) )
- amsrivastavaa3 years agoHelper III
Hi PaulDBrown ,
Its not working as expcted..my requirement is detailed below
1. When User selects all the Year,(Slicer) Amount will be shown only for those Projects which are common to all the Year.
I.E. in above case, as P-1 is only common Project across all year then only P-1 data will be considered .
2. When year selected are 2017 and 2019 only, as only P-1 is common, so data for P-1 will only be considered
3- When year selected are 2017 and 2018 only, as P-1 & P-2 are common so data for both P-1 and P-2 will be considered only
Please suggest!!
Thanks
A
- PaulDBrown3 years agoCommunity Champion
See if this works
A temporary measure to get the correct sum for the common projects:
Rows Values Temp = VAR _CYProjects = CALCULATE ( DISTINCTCOUNT ( fTable[Year] ), ALLSELECTED ( fTable ) ) VAR _ALLProjects = CALCULATE ( COUNT ( fTable[Year] ), FILTER ( ALLSELECTED ( fTable ), fTable[Project] = MAX ( fTable[Project] ) ) ) VAR _CP = CALCULATETABLE ( VALUES ( fTable[Project] ), FILTER ( fTable, _CYProjects = _ALLProjects ) ) VAR _Value = CALCULATE ( SUM ( fTable[Amount] ), _CP ) RETURN _ValueAnd the final measure for the matrix
Common Projects = SUMX( ADDCOLUMNS( VALUES(fTable[Project]), "@Sum", [Rows Values Temp]), [@Sum])- amsrivastavaa3 years agoHelper III
Hi PaulDBrown , Thanks!!! Its working as expected.
Just a little more help needed from you!!
I need to display Growth Rate with respect to previous year selected in the canvas for the common projects available across the year.
So, after having Common Project logic, I need to display Growth Rate across year as detailed below :
1. Lets say user selected YEAR 2017, 2018 and 2019, then I want to show growth rate as below
i.e. For 2017 - as this is least year, so it will be 0
For 2018 = (Actual value of 2018 - Actual value of 2017)/Actual Value of 2017
= (30-10)/10 = 2
For 2019 = (Actual value of 2019- Actual value of 2018)/Actual Value of 2018
= (60-30)/30 = 1
2. Lets say User selected year 2017 and 2019, then growth rate need to be calculated as shown below :
i.e. 2017 : earliest year , so 0
For 2019 = (Actual value of 2019 - Actual value of 2017)/Actual Value of 2017
= (60-10)/10 = 5
3. If lets say user selected year 2018, 2020 and 2022, then growth rate will be calcauted as below
i.e. 2018 : earliest year , so 0
For 2020 = (Actual value of 2020 - Actual value of 2018)/Actual Value of 2018
For 2022 = (Actual value of 2022 - Actual value of 2020)/Actual Value of 2020
Please suggest !!
Thanks
A