Power BI Matrix Report

[amsrivastavaa]

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 

[amsrivastavaa]

Hi @PaulDBrown ,

Could you please help me to have the solution of mentioned query !!

Please suggest !!!

Thanks

A

[PaulDBrown]

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

[amsrivastavaa]

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

[PaulDBrown]

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)

[amsrivastavaa]

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 !!!

@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

[PaulDBrown]

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

[amsrivastavaa]

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

[PaulDBrown]

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

[amsrivastavaa]

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

[PaulDBrown]

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

[amsrivastavaa]

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

[amsrivastavaa]

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

[amsrivastavaa]

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

[amsrivastavaa]

HI @PaulDBrown ,

Could you please provide your immaculate expertize on the above mentioned query!!!

Thank You 

A