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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

v-rzhou-msft

How to calculate about Matrix subtotal

In Power BI Desktop visualizations, matrixes are often used. Mostly the matrix calculations will be involved.

 

Scenarios:

About how to switch subtotal and how to switch subtotal at different levels.

 

Procedures:

Select this matric visual, click on the small brush pattern under the visual image - Format, next click Subtotal, turn off the corresponding row subtotal or column subtotal as appropriate. This will turn on or off subtotal for all levels.

1.png

 

In this case, you can also choose to turn off the subtotal for different levels. At this point we need to create a Measure formula, using the ISINSCOPE function.

Attention: To achieve this, the subtotal above needs to remain open.

2.jpg

 

About ISINSCOPE:  Return true when the specified column is the level in a hierarchy of levels.

More details: ISINSCOPE

 

Sample data:

1.png

 

We need to hide the total of Assurance under the level A1 but still show the total of AA1.

Firstly, we can create a Measure as follows.

 

 

target_measure =
IF (
    ISINSCOPE ( Employees[StaffName] ),
    SUM ( Employees[Target] ),
    IF (
        ISINSCOPE ( Employees[StaffOfficeName] ),
        SUM ( Employees[Target] ),
        IF (
            ISINSCOPE ( Employees[StaffUnitName] ),
            IF (
                MAX ( Employees[StaffUnitName] ) = "Assurance",
                BLANK (),
                SUM ( Employees[Target] )
            ),
            IF ( ISINSCOPE ( Employees[StaffPosition] ), SUM ( Employees[Target] ) )
        )
    )
)

 

 

 

In this formula, we should first find Assurance is under the level of A1, that is, in the StaffUnitName column, other levels do not change, in this level if this column has the value = Assurance, then itwill output blank, or you can output the value to text instead of blank.

1.jpg

 

Then the rest will output sum (target). Similarly, if you want the subtotal of a field under other levels not to be displayed, find the corresponding Level, and get the corresponding result by using the if function and ISINSCOPE function.

1.png

 

Scenarios:

We can display the subtotal as SUBTRACTION instead of SUM of the values.

 

Sample data:

1.png

 

For example, the expected output of total is -6, 2, 2, -6, respectively, at this time we can control the value of total by creating a measure.

At this point, we can use the HASONEVALUE function. About HASONEVALUE: Return TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.

More details: HASONEVALUE

Then create a measure like the following.

 

 

hasonevalue =
VAR _aa1 =
    CALCULATE (
        SUM ( Employees[Target] ),
        FILTER (
            ALL ( Employees ),
            Employees[StaffUnitName] = "AA1"
                && Employees[StaffOfficeName] = SELECTEDVALUE ( Employees[StaffOfficeName] )
        )
    )
VAR _ASSURANCE =
    CALCULATE (
        SUM ( Employees[Target] ),
        FILTER (
            ALL ( Employees ),
            Employees[StaffUnitName] = "Assurance"
                && Employees[StaffOfficeName] = SELECTEDVALUE ( Employees[StaffOfficeName] )
        )
    )
RETURN
    IF (
        HASONEVALUE (Employees[StaffName] ),
        MAX ( Employees[Target] ),
        _aa1 - _ASSURANCE
)

 

 

 

If the context of Employees[StaffName] is filtered to a different value, the value in the dataset is returned, otherwise the corresponding value can be obtained by calculation based on the desired output.

1.png

 

Summarize:

Through the above study, I believe you can skillfully use the matrix.

 

 

Author: Polly Pu

Reviewer: Kerry Wang & Ula Huang