Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago
Solved

Power BI Matrix Report

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 ...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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
    _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

     


     

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

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's avatar
amsrivastavaa
Helper III
3 years ago

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