Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
amsrivastavaa
Helper III
Helper III

Power BI Matrix Report

Hi Guys!!

 

I have data as below 

 

TypeProjectYearAmount
CreditP-1201710
CreditP-2201720
CreditP-1201830
CreditP-2201840
CreditP-3201850
CreditP-1201960

 

Ideally, If will create Matrix Visuals on the above data, it looks like as below

Type201720182019
Credit3012060

 

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 : 

Type201720182019
Credit307060

 

 

Note : As per requirement, SLicer for Project is not allowed to create.

 

Thnks

Amit 

 

1 ACCEPTED SOLUTION

See if this works for you.

First the model

model.jpg

 

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:

PyP.gif

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."

rep 3.jpg

 

 

 

Sample file attached

 


 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

34 REPLIES 34

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"

tooltip.jpg

Once it is set up, you can hide the year slicer:

Hide slicer.gif

and you will get this

Tooltip result.gif

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" )

 

no comm projects message.jpgSample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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 : 

 

amsrivastavaa_0-1669701379441.png

 

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)

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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.

 

amsrivastavaa_0-1669726621119.png

 

* Net Profit too need to follow Common Project logic, 

I.e. Need to display data as below

 

amsrivastavaa_0-1669743658259.png

 

Please suggest !!!

@PaulDBrown 

 ----------------

 

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

Net Profit.jpg

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

 

URLhttps://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.

 

amsrivastavaa_0-1669884983165.png

 

 

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])

GRNP.jpg

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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.

 

amsrivastavaa_0-1670006307547.png

 

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] )

v6.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.