Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Guys!!
I have data as below
Type | Project | Year | Amount |
Credit | P-1 | 2017 | 10 |
Credit | P-2 | 2017 | 20 |
Credit | P-1 | 2018 | 30 |
Credit | P-2 | 2018 | 40 |
Credit | P-3 | 2018 | 50 |
Credit | P-1 | 2019 | 60 |
Ideally, If will create Matrix Visuals on the above data, it looks like as below
Type | 2017 | 2018 | 2019 |
Credit | 30 | 120 | 60 |
But I need to create Matrix report where Sum of Amount will be consider only for those Project which are common to previous Year projects, i.e.
As there are two Projects (P-1 and P-2) which are common to Year 2017 and 2018, i want to have only Amount for these two project, so in this case For 2017, Amount for Year 2017 is 10 and 20 for Project P-1 and P-2 and these too are avaialble in 2018, so it will 10+20 =30 for Year 2017.
For 2018, though it has total three project (P-1, P-2 and P-3) where summation of Amount = 30+40+50=120 but I want to display data only for P-1 and P-2 as these are commmon to previous year, so in this case in will be 30+40=70.
And so on for 2019, i.e. only Year P-1 for Year 2019.
Outcome required is :
Type | 2017 | 2018 | 2019 |
Credit | 30 | 70 | 60 |
Note : As per requirement, SLicer for Project is not allowed to create.
Thnks
Amit
Solved! Go to Solution.
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
_result
Common 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
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
Could you please help me to have the solution of mentioned query !!
Please suggest !!!
Thanks
A
By the answer to my question I understood you wanted the distinctcoun over all projects, not over the common projects.
See if this works:
The distinctcount measure is:
Distinctcount CP =
CALCULATE(
DISTINCTCOUNT('Project Table'[Project]), FILTER('Project Table', NOT ISBLANK([Common Projects])))
You can now set up the tooltip as follows with a card visual and de [Distinctcount CP] measure. You will need to include a year slicer in the tooltip page synched with the year slicer in the report page (otherwise the tooltip will ignore the yearly comparisons). For the filters, add model and type and turn off the option "keep all filters"
Once it is set up, you can hide the year slicer:
and you will get this
Optionally, for the card visual in the report page, you can use the following measure if you would like to to show a message of "No common Projects" if there are none:
Distinctcount Common Projects (text) =
VAR _DC =
CALCULATE (
DISTINCTCOUNT ( 'Project Table'[Project] ),
FILTER ( 'Project Table', NOT ISBLANK ( [Common Projects] ) )
)
RETURN
IF ( _DC > 0, FORMAT ( _DC, "#0" ), "No common projects" )
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
Thanks for the the above logic.
Its works for most of the scenario however it failing for one.
I am detailing as below :
Source Data :
I have change few records and data looks like as above,i.e. for Year=2021, we have new project= P-2 and there is no data for rest of year for AMOUNT.
As this project is new to the system, and only having AMOUNT in 2021, so this should not be a part of data when user selects all year <2017, 2018, 2019, 2020, 2021>, as P-2 has no data for amount for rest of the year except 2021.
So, requirement is P-2 data must not be part of either Matrix data and it should not be considered for Project count.
I am sharing URL for the PBIX
https://1drv.ms/u/s!Ahtm7otFIxr8eHxerSgT_TsP8ik?e=rE2Tjh
Thanks
A
Change the [Rows Values Temp] measure 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] )
&& NOT ISBLANK ( [Sum Amount] )
)
)
VAR _CP =
CALCULATETABLE (
VALUES ( fTable[Project] ),
FILTER ( fTable, _CYProjects = _ALLProjects )
)
VAR _result =
IF ( MAX ( fTable[Project] ) IN _CP, [Sum Amount] )
RETURN
_result
(BTW, if these rows without an amount value have no other relevant data, you should ideally remove them on import since they add no value and will only bloat the model)
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown , Thanks Its working well now...
(BTW, if these rows without an amount value have no other relevant data, you should ideally remove them on import since they add no value and will only bloat the model)
:Amit - Icannot do that as this dataset has been used in another PBI Builder report
======================================================
Just a little more help needed, which I detailed below
I am getting one more Type i.e. Net Profit along with Credit and Debit from the source with some values in it however I want to calculate Net Profit based on the values Debit and Credit shown in the Matrix Visual
I need to calculate Net Profit based on running values of Credit and Debit for that year rather than what coming from the source.
Calculation for Net Profit = (Credit-Debit)/Debit
For below mentioned data, we have Credit=0 and Debit=20; Net profit will be calculated as above.
* Net Profit too need to follow Common Project logic,
I.e. Need to display data as below
Please suggest !!!
----------------
Hi @PaulDBrown ,
I have added the records as required in PBIX with issue detailed in 30 Nov tab in PBIX.
Please find the same at below URL
https://1drv.ms/u/s!Ahtm7otFIxr8eeHmFyKgUlv8kDA?e=95WfRg
Thanks
A
Hi there! Use this measure:
Comon Projects with Net Profit =
VAR _C =
CALCULATE (
[Common Projects],
FILTER ( ALL ( 'Type Table' ), 'Type Table'[Type] = "Credit" )
)
VAR _D =
CALCULATE (
[Common Projects],
FILTER ( ALL ( 'Type Table' ), 'Type Table'[Type] = "Debit" )
)
VAR _NP =
DIVIDE ( _C, _D ) - 1
VAR _Result =
IF (
MAX ( 'Type Table'[Type] ) = "Net Profit",
FORMAT ( _NP, "#0.00" ),
[Common Projects]
)
RETURN
_Result
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
I have tried to use new measure in normal data and so far its working fine!! Thanks to you 🙂
However, when I tried to replicate for the Growth Rate Matrix, its not showing correct result. (Observation : not working at all for Net Profit i.e. it considering all the projects rather than common projects)
Could you please let me know how to have growth rate of all the KPI across years with only common projects.
I hereby sharing link of the PBIX for what I required on 1 dec- requirement tab
URL : https://1drv.ms/u/s!Ahtm7otFIxr8erv045EekUrGfCw?e=oN2isr
Normal Data : Once I have used new Comon Projects with Net Profit
GROSS RATE FOR NORMAL DATA : This is required, it is growth rate with respect to previous year with COMMON PROJECT LOGIC.
Formula For Growth Rate = (for e.g (2017 -2018 year) = let say for Net Profit
GR = (Net Profit of 2018 - Net Profit of 2017)/Net Profit of 2017
** Need these growth rate under the umbrella of Common Project logic.
Please suggest !!!
Thanks
Amit Srivastava
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
Proud to be a Super User!
Paul on Linkedin.
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
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] )
Proud to be a Super User!
Paul on Linkedin.
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
HI @PaulDBrown ,
Could you please provide your immaculate expertize on the above mentioned query!!!
Thank You
A
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
127 | |
109 | |
93 | |
70 | |
67 |