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 @amsrivastavaa ,
Whether the advice given by @PaulDBrown has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry
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 ) )
Proud to be a Super User!
Paul on Linkedin.
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
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
_Value
And the final measure for the matrix
Common Projects =
SUMX(
ADDCOLUMNS(
VALUES(fTable[Project]), "@Sum", [Rows Values Temp]), [@Sum])
Proud to be a Super User!
Paul on Linkedin.
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
This should work. FIrst of all, I've added a Year dimension table to make things easier:
Then a new temp measure:
PY Temp =
VAR _PY =
CALCULATE (
MAX ( 'Year Table'[dYear] ),
FILTER (
ALLSELECTED ( 'Year Table'[dYear] ),
'Year Table'[dYear] < MAX ( 'Year Table'[dYear] )
)
)
VAR _PYValue =
CALCULATE (
SUM ( fTable[Amount] ),
FILTER ( ALL ( 'Year Table' ), 'Year Table'[dYear] = _PY )
)
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 )
)
RETURN
IF ( MAX ( fTable[Project] ) IN _CP, _PYValue )
and the final Growth measure:
Growth Rate =
VAR _PYValue = SUMX(ADDCOLUMNS(VALUES(fTable[Project]), "@PY", [PY Temp]), [@PY])
VAR _result = DIVIDE([Common Projects]- _PYValue, _PYValue)
RETURN
COALESCE(_result, 0)
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown , Thanks for the reply.
It's working well but with few exception.
1. I have introduced DEBIT as a type in the data and introduced Type filter in the report canvas, then, I f I am selecting multiple values i.e. both Debit and Credit, its not showing current data.
2. I have introduced another column in the Data say Model and introduced Model as a slicer, then its not working as expected.
3. Also, Growth Rate will show data only for those projects which are common to all years selected.
3.1
Lets Say User selected Year 2017, 2018 and 2019.
And, P-1 is available for Year 2017 and 2019
And P-2 is available for Year 2018 and 2019
And P-3 is avilable for Year 2017 and 2018
Then, as no project is common in all the year, it grwoth rate matrix should not show any data.
3.2
Lets Say User selected Year 2017, 2018 and 2019.
And, P-1 is available for Year 2017,2018 and 2019
And P-2 is available for Year 2018 and 2019
And P-3 is avilable for Year 2017 and 2019
Then, as P-1 is available for all the projects, it must be considered and Growth Matrix will display its data.
I am sharing PBIX URL and i have detailed issue in detail.
https://1drv.ms/u/s!Ahtm7otFIxr8duY8CzdqQr0ybsA?e=RpITZo
Please suggest, that would be very helpful.
Thanks
A
@amsrivastavaa , Try a measure like
Measure = var _tab = SUMMARIZE(FILTER(ALLSELECTED(Data2), Data2[Year] = max(Data2[Year])-1), Data2[Project])
return
CALCULATE(SUM(Data2[Amount]), FILTER(Data2, Data2[Project] in _tab || ISBLANK(COUNTX(_tab,[Project]))))
HI @amitchandak , Thanks for the reply.
I have placed PBIX at below URL with my requirement.
URL : https://1drv.ms/u/s!Ahtm7otFIxr8c5QlXvBfzm_pCHs?e=PDtuUk
I have incorporated above in the same PBIX and detailed my requirement as well.
Please provide your valuable suggestion.
Thanks
A.
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 ,
Apologies for reverting late!! Was out of work becasue of Bad health.
Anyways, Today, I have gone through the solution and it is working well, it is just an example of your excellence!!
I have few very little requirement on top of that!! May be, I am asking a bit more, but thats how things are
1. I want to show Total number of distinct Project used across matrix based on Year selected.
For, e.g. If user selected say Year = 2018, 2019 and 2020 and I have data as below
Type | 2018 | 2019 | 2020 |
Credit | 566 | 454 | 4545 |
Debit | 343 | 45 | 34 |
Let say for Credit, Total number of distinct project used are acorss years (For 2018 : #project =2 ; For 2019 : #project =2 ;For 2020 : #project =2 ) i.e. 2
But,for Debit, Total number of distinct project used are acorss years (For 2018 : #project =3 ; For 2019 : #project =3 ;For 2020 : #project =3 ) i.e. 3
So, I want to display TOTAL NUMBER OF DISTINCT PROJECT on the CARD, i.e 3 (as this is maximum)
2. Lets say, User selected Type=Credit in FILTER, then need to display TOTAL NUMBER OF DISTINCT PROJECT on the CARD, i.e 2
3. Also, if there is another filter say, Model, and User selected Model=A, then, in available data, what ever will be TOTAL NUMBER OF DISTINCT PROJECT on the CARD, are will get display over the CARD.
4. I want to include ToolTip on every entry of amout that will say number of project used for particular year for particular TYPE.
i.e. If user hovers around 566 value, i,e. Type is Credit and Year is 2018, then tool tip will show Number of project as 2.
Please suggest!!
Thanks
A
For the tooltip, do you just need the number or the number & the list of projects?
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
I need just a distinct count of project for that year and its previous year only.
When user hover on amount of Year=2018 and Type=Credit , then he/she can see data something like this.
2017 | 2018 | |
Number of Project | 2 | 2 |
Thanks
A
I'm glad you have recovered!
As regards you last request, is the data you are referring to the same as the sample file?
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown , Thanks for your gesture!!
Yes, sample file is same as you have shared last time with me.
Thanks
A
Another question..
Do you need the distinct count over all projects or only of those which are persent in the selected years?
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
Only for the data, which are available after appling all the filters including YEAR, TYPE, etc.
Thanks
A
Ok see if this works for you.
New measures:
For the matrix
Sum Amount =
SUM(fTable[Amount])
For the tooltip page
Distinctcount Projects =
DISTINCTCOUNT(fTable[Project])
For the card
Max distinctcount =
MAXX (
ADDCOLUMNS ( VALUES ( 'Type Table'[Type] ), "@DC", [Distinctcount Projects] ),
[@DC]
)
Set up the tooltip page with the [Distinctcount projects] measure (I changed the name for the visual) and add the 'Type Table'[Type] and the 'Year Table'[dYear] as the filters.
and you will get the following:
Sample file attached
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
Unfortunatly, its not working as expected, it loosing basic functionality of common project for all the year (which is detailed above and you have guided me for the solution already)
So, implemenetion of these measure will be on top of that functionality of common project criteria.
I have detailed my requirement in PBIX under Today's date tab, please find the link for the same.
https://1drv.ms/u/s!Ahtm7otFIxr8d75HKWwpAvZcKBU?e=minbTX
Please suggest!!
Thanks
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 |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |